Forum Discussion
IF Function problems
Hi,
I've written an IF rule to allocate exam results but I can't copy the formula without the reference changing. Is there a way around this or do I have to re-do the reference every time?
Also, I'd like to colour code the IF cells too but can't seem to make this work. Here's what I've got so far:
Thanks
Catherine
Hello,
do you have a table with static values in cell A44 to A48? Do you want the formula to always look at these cells? Then you can use absolute references, with $ signs in front of the column letter and the row number, i.e. $A$44 instead of A44. Then, when the formula is copied down, it will still refer to $A$44.
Is there a reason you put the numbers into quotes, like "85" instead of just 85? That will return them as text and you can't easily use the values in calculations later on.
It is also better to use a lookup formula to look up the values instead of hard-coding them into the formula. That way, if your level definitions ever change, you only need to change the number in one place. For example, put the threshold values into A44 to A49 and in B44 to B49 put the result values. then you can use
=VLOOKUP(I3,$A$44:$B$49,2,TRUE)
With TRUE as the last parameter, Vlookup will use an approximate lookup which is perfect for lookups for numbers in a range from x to z.
Hi,
Thank you so much for your help, using the VLOOKUP that you suggested worked perfectly!
Can you / anyone else help with the conditional formatting that I'd like to use to colour code the cells in column J? I still can't get it to work.
thanks
Catherine
Hi again,
On closer inspection the VLOOKUP hasn't worked - it isn't showing the correct results. J10 & J14 should read 90, J7, J8 & J15 should read 95. I'm really not familiar with this so I've probably done something silly! I've attached a screenshot
Trouble is that the 37 % isn't in your block of answers for the vlookup and then will give the last value that fits e.g. after the 20% There will be masters here that can give better answers but I would make a long if statement or make a new table from 1 to 100 on another tab and use that one for the vlookup. The table on the current can stay there for reference but wil be not used by excel then .
regards,
Willie
