Getting a FALSE value from this formula, weird formula behaviour

**PMcG** · 11-30-2012, 03:54 PM

Hello All,

My first time on this forum, normally I can figure out what is going on but this has got me completly stumped.

I am making a spreadsheet for marking (I am a teacher) and have come up with the following formula:

=IF(G20="","",IF(G20="5+",Master!C20*0.98,IF(G20="5",Master!C20*0.95,IF(G20="5-",Master!C20*0.91,IF(G20="4+",Master!C20*0.88,IF(G20="4",Master!C20*0.85,IF(G20="4-",Master!C20*0.81,IF(G20="3+",Master!C20*0.78,IF(G20="3",Master!C20*0.75,IF(G20="3-",Master!C20*0.71,IF(G20="2+",Master!C20*0.68,IF(G20="2",Master!C20*0.65,IF(G20="2-",Master!C20*0.61,IF(G20="1+",Master!C20*0.58,IF(G20="1",Master!C20*0.55,IF(G20="1-",Master!C20*0.51,IF(G20="R",Master!C20*0.45)))))))))))))))))

The idea is that you can input a range of "levels" in a cell 4 over and that it will report in this cell a percentage of a value from another sheet(percentage value of a mark) to be used for total mark calculations.

In cell G20 I have a 3, and in Master!C20 I have a 10 yet I am getting a result of FALSE. The only things that will be entered in G20 are R,1-,1,1+,2-,2,2+,3-,3,3+,4-,4,4+,5-,5,5+.

Now the weird thing is the first time that I put in these values it worked, but I then erased the 3 in G20 and changed it to a 5 and it started giving me FALSE values and is now stuck on that no matter what is entered in Master!C20 and G20.

I have used IF statements all over the rest of my spreadsheet and they have worked perfectly, this is the first time that I have run into probelms but for the life of me cant see what it is.

Any help would be much appreciated!

Thanks.

**oeldere** · 11-30-2012, 03:58 PM

Tip: use VLookup to search the value of G20 in a table.

**Moo the Dog** · 11-30-2012, 04:16 PM

See my attached workbook. It uses a VLOOKUP to match the multiplier to the level entered in G20, then multiplies it with the value in C20 on the 'Master' sheet. Plus, the formula used is now just:

=VLOOKUP(G2,Lookups!$A$2:$B$17,2,0)*Master!$C$20

instead of that long nested IF statement.

- Moo

Note: In my sheet, the "level" is in G2, thus the VLOOKUP formula refers to G2. If the "level" you wanted to work with was in G20, then G2 would be changed to G20

**PMcG** · 11-30-2012, 04:24 PM

Thank you so much, I have spent the last 20 mins trying to figure out oeldere's suggestion but it makes sense now, I am going to try it out and hopefully it solves all my problems. Thanks!

**Moo the Dog** · 11-30-2012, 04:30 PM

You're welcome. Glad to help. Post back here if you have any questions.

- Moo

**martindwilson** · 11-30-2012, 04:37 PM

=master!C20*LOOKUP(G20&"",{"1","1-","1+","2","2-","2+","3","3-","3+","4","4-","4+","5","5-","5+","R"},{0.55,0.51,0.58,0.65,0.61,0.68,0.75,0.71,0.78,0.85,0.81,0.88,0.95,0.91,0.98,0.45})

**PMcG** · 11-30-2012, 04:58 PM

Thanks martin for the suggestion in one cell, I think I am going to go with the chart because there are multiple areas in the spreadsheet that I have to convert back and forth between levels and marks.