If a cell contains a list value then assign formula X, Y, Z?

Question

Hello,&nbsp;I have a spreadsheet that is being used for insurance purposes. I have a column that has a list set up through Data Validation. I am trying to create a formula that will essentially do this ...#1. If the cell contains List Value Auto, Home, etc. then ...#2. assign formula X, Y, Z, etc. to a cell in this column which is calculating a percentage of #3&nbsp;The formula in column #3 is a simple "=Fxx*0.09" but I need the percentage to be tied to a specific list value from #1. Not sure best route. Open to anything.Thanks for the help!&nbsp;

martin_weiss · Answer

Hi&nbsp;MyCrymbals82&nbsp;&nbsp;you could create a reference table with the percentages per product. That table could be anywhere in your workbook. In my example, I just put it beside of your entry table, but that does not matter:&nbsp;=I2*VLOOKUP(F2;$O$2:$P$6;2;FALSE)&nbsp;So, depending on the product entered in cell F2, it looks for the matching percentage in the reference table and multiplies the premium with it.

mycrymbals82 · Answer

Martin_Weiss&nbsp;&nbsp;Thanks for your reply. I'm closer now but for some reason it doesn't like the Policy Type, cell E3 (Product in your example). No idea why because there is nothing on that field.=F3*VLOOKUP(E3;$L$2:$P$6;2;FALSE)&nbsp;&nbsp;

martin_weiss · Answer

Hi&nbsp;MyCrymbals82&nbsp;&nbsp;what happens if you enter a policy type in E3. Do you still get an error?If E3 is empty, there will be an error because the formula tries to find an empty value in the lookup table, which does not exist there.To avoid this, you could also enhance the formula like this:=F3*IFERROR(VLOOKUP(E3;$L$2:$P$6;2;FALSE);0)

mycrymbals82 · Answer

Martin_Weiss&nbsp;&nbsp;Thanks again for the reply. I posted this to an Excel Facebook group and the issue was the semi-colons. If I change them to commas, the formula works. I was even able to add a validation list to the Policy Type column so users can pick from a drop down.&nbsp;=F3*VLOOKUP(E3,$L$2:$M$7,2,FALSE)&nbsp;Only issue is the Total (pointed to in arrow #1) doesn't do the sum of the column unless every row has a currency value in Agency Comm. That formula is a simple =SUM(H3:H20). I assume the #N/A is the reason.&nbsp;

martin_weiss · Answer

Hi&nbsp;MyCrymbals82&nbsp;&nbsp;that's true, the regular SUM function cannot handle #N/A errors. You could use SUMIF instead:&nbsp;=SUMIF(E2:E15,"&lt;&gt;#N/A")&nbsp;Alternatively you could use the AGGREGATE function:=AGGREGATE(9,6,E2:E15)This one ignores all types of error.&nbsp;Just change the range E2:E15 to the relevant range in your worksheet, that should fix the problem.

Forum Discussion

If a cell contains a list value then assign formula X, Y, Z?

5 Replies

Resources