Results 1 to 9 of 9

Thread: Formula help

  1. #1

    Formula help



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hello oh mighty GURUs,

    Security analysis.xlsxI have attached a simple file in which the end result needs to be the calculation of 2 columns with 5 options in each to select from, "Scale" and "Ranking". The sub total column I am assuming is where this calculation will occur and then I need to total each variable down and get a total total. Hope this makes sense cause I am in a pinch.

  2. #2
    Please provide an example of what it will look like when completed.

    Just a few rows will probably suffice unless this is more involved than adding cells across the row.

  3. #3
    Thanks for the response, I was hoping there may be a way to do one formula instead of 6 different columns to do one for each, see new attachment. I did a calculation so you could see what i am trying to accomplish, I used check marks but I know they wouldn't work in a calculation, probably could use a zero or one with an if statement but not sure.
    Attached Files Attached Files

  4. #4
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,077
    Articles
    0
    On any single row, can there be more than one check mark in columns D to H (and can there be none at all)?

    ps. mystified: how did you get those check marks in the cells?
    Last edited by p45cal; 2017-05-19 at 02:44 PM.

  5. #5
    Quote Originally Posted by p45cal View Post
    On any single row, can there be more than one check mark in columns D to H (and can there be none at all)?
    No, there will only be 1 selection between D-H but also there is no need for a check mark, I just utilized it to identify a selection. The basic premise that I can't figure out is having 2 sets of selection criteria to produce a specific result without having to do several columns of calculations and then finally adding the total. if that is how it has to be then it is what it is.
    Last edited by bobjglover@gmail.com; 2017-05-19 at 02:44 PM.

  6. #6
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,077
    Articles
    0
    Try in cell I6:
    =IFERROR($C6*INDEX({0.4,0.3,0.1,0.1,0.1},MATCH("?",$D6:$H6,0))/10,"")
    and copy down 5 cells. Copy resultant block 3 times further down.
    You should be able to do the totals in column J yourself, right?

    ps. mystified: how did you get those check marks in the cells?

  7. #7
    Quote Originally Posted by p45cal View Post
    Try in cell I6:
    =IFERROR($C6*INDEX({0.4,0.3,0.1,0.1,0.1},MATCH("?",$D6:$H6,0))/10,"")
    and copy down 5 cells. Copy resultant block 3 times further down.
    You should be able to do the totals in column J yourself, right?

    ps. mystified: how did you get those check marks in the cells?
    Ok this works, I new it could be done.I can also use anything in that cell to generate the action, thank you! Now can you explain to me how this "call" works - INDEX({0.4,0.3,0.1,0.1,0.1} and does the question mark allow for any entry in those cells?

    BTW: the check mark is just an inserted symbol...

  8. #8
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,077
    Articles
    0
    If you put each of:
    40% 30% 10% 10% 10%
    into each of cells L2:P2 respectively (these are the percentages in brackets in your cells D3:H3)
    Then in any other cell put the formula:
    =INDEX(L2:P2,2)
    you should get the result 0.3 (the 2nd value is .3 (=30%))
    Go back into editing that formula and highlight only the L2:P2 and then press F9 on the keyboard. This converts the range reference to hard values. That's what I used in the formula.
    If you had put values into L2:P2 the formula would have been:
    =IFERROR($C6*INDEX($L$2:$P$2,MATCH("?",$D6:$H6,0))/10,"")
    Doing it that way makes it easier to adjust your percentages - just adjust them in L2:P2 (or wherever you choose to put them).
    Last edited by p45cal; 2017-05-19 at 10:13 PM. Reason: corrected the red highlighting

  9. #9
    Quote Originally Posted by p45cal View Post
    If you put each of:
    40% 30% 10% 10% 10%
    into each of cells L2:P2 respectively (these are the percentages in brackets in your cells D3:H3)
    Then in any other cell put the formula:
    =INDEX(L2:P2,2)
    you should get the result 0.3 (the 2nd value is .3 (=30%))
    Go back into editing that formula and highlight only the L2:P2 but and then press F9 on the keyboard. This converts the range reference to hard values. That's what I used in the formula.
    If you had put values into L2:P2 the formula would have been:
    =IFERROR($C6*INDEX($L$2:$P$2,MATCH("?",$D6:$H6,0))/10,"")
    Doing it that way makes it easier to adjust your percentages - just adjust them in L2:P2 (or wherever you choose to put them).
    OMG I can't believe I ask such a stupid question I get it now however I didn't know or didn't read that you had put the locations in L2 2p2 I just put the formula in the subtotal column and it worked fine

    You can pretty much close this one another resolved thanks so much
    Last edited by p45cal; 2017-05-19 at 10:12 PM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •