Formula help

B

bobjglover@gmail.com

New member
Joined
Aug 3, 2015
Messages
57
Reaction score
0
Points
0
Excel Version(s)
1901
Hello oh mighty GURUs,

View attachment 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.
 
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.
 
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.
 

Attachments

  • Security analysis.xlsx
    11.3 KB · Views: 12
  • Security analysis.xlsx
    13.6 KB · Views: 13
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:
p45cal said:
On any single row, can there be more than one check mark in columns D to H (and can there be none at all)?
Click to expand...

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:
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?
 
p45cal said:
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?
Click to expand...

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...
 
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:
p45cal said:
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).
Click to expand...

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 a moderator:
You must log in or register to reply here.
Back
Top