Solved Nested IF Function - Excel 2010

April 13, 2012 at 07:09:59
Specs: Windows 7
Hi All,

I was hoping to get some help with writing an IF statement in excel 2010. I basically have a column for frequency and a column for risk like the following:


I basically need a formula that knows for example:
If quarterly AND high then the value in the cell will equal 90...or if monthly and low the value would equal 60...and so on.

Thanks for any help you can provide!!!

See More: Nested IF Function - Excel 2010

Report •

April 13, 2012 at 11:11:21
✔ Best Answer
A better option might be a table with Frequency down the side and Risk across the top, like:

       A         B         C     D
 1)         	Low	Medium	High
 2) Annually	 54	  72	 86
 3) Bi-Annually  56	  74	 88
 4) Quarterly	 58	  76	 90
 5) Monthly	 60	  80	 92
 6) Weekly	 62	  82	 94
 7) Daily	 64       84     96
 9) Frequency
10) Risk
12) Value

Now enter this formula in Cell B12


Now when you enter a Frequency in cell B9
and when you enter a Risk in cell B10

The formula will get the value of the intersection of the two from the table.

IE: B9 = Quarterly
B10 = Medium
B12 should return 76

Is this something you can use?
Or do you really want an =IF() function to tries to cover all the possible combinations above.


Report •

April 13, 2012 at 11:51:30
To take Mike's fine suggestion one step farther, consider using Drop Down Validation lists in B9 and B10, with the lists populated with the values from A2:A7 and B1:B3 respectively,

That way there would be no danger of a user entering something in one of those cells that could cause an error.

The other advantage of the table method (and the drop downs) is that all changes are made to the table, not to any formulas or hardcoded data validation lists.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

Related Solutions

Ask Question