Forum Discussion

Catherine Johnston's avatar
Catherine Johnston
Copper Contributor
Oct 14, 2018

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...
Excel
Formulas and Functions
Catherine Johnston's avatar
Catherine Johnston
Copper Contributor

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

 

Willie de Wit's avatar
Willie de Wit
Copper Contributor
Oct 15, 2018

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

  • Willie de Wit's avatar
    Willie de Wit
    Copper Contributor
    Oct 15, 2018

    The longer if statement being '=IF($E$43<A44,B44,IF($E$43<A45,B45,IF($E$43<A46,B46,IF($E$43<A47,B47,IF($E$43<A48,B48,IF($E$43<A49,B49))))))'

     

    Keep in mind I absolute adressed the $e$43. on that spot should be the cell you check .

    Hope it helps a bit

     

    For the coloring you can use the standard conditional formatting for one color. You just have to put them in right order since the will be examined in the list order you see when you open conditional rules.

Resources