Formula Problem

[Puddycat]

I want cell E2 to calculate a SUM of cell C2 and D2. C2 and D2 are formatted with drop downs with the below values:

C2
Extremely Serious
Major
Severe
Serious
Low

D2
Very Unlikely
Unlikely
Potentially
Possible
Probable
Virtually Certain

I thought the following formula would work, but it is not. Any suggestions on this one?

{=SUM(IF(C2{"Extremely Serious";"Major";"Severe";"Serious";"Low"},{5;4;3;2;1}))}+({(IF(D2={"Very Unlikely";"Unlikely";"Potentially";"Possible";"Probable";"Virtually Certain"},{1;2;3;4;5;6})))}

 

[T. Valko]

I want cell E2 to calculate a SUM of cell C2 and D2. C2 and D2 are formatted with drop downs with the below values:

C2
Extremely Serious
Major
Severe
Serious
Low

D2
Very Unlikely
Unlikely
Potentially
Possible
Probable
Virtually Certain

I thought the following formula would work, but it is not. Any suggestions on this one?

{=SUM(IF(C2{"Extremely Serious";"Major";"Severe";"Serious";"Low"},{5;4;3;2;1}))}+({(IF(D2={"Very Unlikely";"Unlikely";"Potentially";"Possible";"Probable";"Virtually Certain"},{1;2;3;4;5;6})))}

Use cells to hold all the criteria:

Book1
	G	H
1	C2	D2
2	Low	Very Unlikely
3	Serious	Unlikely
4	Severe	Potentially
5	Major	Possible
6	Extremely Serious	Probable
7		Virtually Certain
Sheet1

Then this formula:

=IF(C2<>"",MATCH(C2,G2:G6,0))+IF(D2<>"",MATCH(D2,H2:H7,0))

 

[Jonmo1]

You just had a couple typos in your formula

try

=SUM(IF(C2={"Extremely Serious";"Major";"Severe";"Serious";"Low"},{5;4;3;2;1}))+(SUM(IF(D2={"Very Unlikely";"Unlikely";"Potentially";"Possible";"Probable";"Virtually Certain"},{1;2;3;4;5;6})))

But I prefer Biff's solution.
Always easier to put your varibles in a range, rather than hard code them in the formula. Makes for easier adjustment later on.

 

[Puddycat]

C2 and D2 are not actually choices. They represent Column C, Row2 and Column D, Row2.
In my formula I need each drop down option to have a numerical value associated with it that will actually allow me to get to a numerical SUM.

Extremely Serious = 5
Major = 4
Severe = 3
Serious = 2
Low = 1

Very Unlikely = 1
Unlikely = 2
Potentially = 3
Possible = 4
Probable = 5
Virtually Certain = 6

 

[Puddycat]

I think you almost have me on the right track jonmo. But your formula is calculating the incorrect SUM for some reason.

If I have choices selected of Extremely Serious (5) + Unlikely (2), your formula is giving me a SUM of 2 instead of 7.

 

[Jonmo1]

Works for me, check your spelling

Excel Workbook
	C	D	E	F
2	Extremely Serious	Unlikely		7
Sheet1

 

[Jonmo1]

I think you almost have me on the right track jonmo. But your formula is calculating the incorrect SUM for some reason.

If I have choices selected of Extremely Serious (5) + Unlikely (2), your formula is giving me a SUM of 2 instead of 7.

Look closely at Biff's suggestion...
C2 and D2 where only entered in G1 and H1 for display purposes.
The formula he wrote does not refer to those cells..

Sheet1

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 115px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 115px"><COL style="WIDTH: 100px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD>C2</TD><TD>D2</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD>Extremely Serious</TD><TD>Unlikely</TD><TD style="TEXT-ALIGN: right">7</TD><TD> </TD><TD>Low</TD><TD>Very Unlikely</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD>Serious</TD><TD>Unlikely</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD>Severe</TD><TD>Potentially</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD>Major</TD><TD>Possible</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD>Extremely Serious</TD><TD>Probable</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD>Virtually Certain</TD></TR></TBODY></TABLE>
<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00; BORDER-BOTTOM-COLOR: #00ff00; COLOR: #000000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #00ff00; BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-SIZE: 9pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=2 border=1><TBODY><TR style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #cacaca"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>E2</TD><TD>=IF(C2<>"",MATCH(C2,G2:G6,0))+IF(D2<>"",MATCH(D2,H2:H7,0))</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

 

[Puddycat]

Thanks Guys. I now have it figured out.

 

[T. Valko]

C2 and D2 are not actually choices. They represent Column C, Row2 and Column D, Row2.
In my formula I need each drop down option to have a numerical value associated with it that will actually allow me to get to a numerical SUM.

Extremely Serious = 5
Major = 4
Severe = 3
Serious = 2
Low = 1

Very Unlikely = 1
Unlikely = 2
Potentially = 3
Possible = 4
Probable = 5
Virtually Certain = 6

C2 and D2 are just headers to show which variables belong to which cell drop down.

The formula I suggested does not reference these header cells.