Forum Discussion
Solved
Assistance with a formula
Hello and thanks in advance for any help!
I have a formula that is driving me mad. I'm sure it will be simple for anyone else.
The number in I4 will change.
The numbers in D19:D21 will change dependant on this.
I need the numbers in H19:H21 to show only if they fall within a range of numbers.
I managed to do H21 because I only needed >560. Formula =IF(I4>560,D21,0)
I have been trying to show H19 showing the same value as D19 only if I4 is between 75 to 189
Also H20 showing the same value as D20 if I4 is between 190 to 559.
I will be thankfull for any guidance!
Hi Jo,
If your ranges are in Q and R columsn use for H19
=IF(($I$4>=$Q4)*($I$4<=$R4),D19,0)
and copy that cell down to H20 and H21.
Hi Jo Samat,
Apply this in H19 Cell
=IF(AND(I4>=75,I4<190),D19,0)
Apply this in H20 Cell
=IF(AND(I4>=190,I4<560),D20,0)
I attached the same in file.
Thanks so much Logaraj!
That works a treat too
Much appreciated :-)
Hi Jo,
If your ranges are in Q and R columsn use for H19
=IF(($I$4>=$Q4)*($I$4<=$R4),D19,0)
and copy that cell down to H20 and H21.
Hi Sergei,
That worked a treat - thank you so much!
I would like to say it makes sense now, but i will leave it as it seems like magic :-)
Jo, no magic if remember what TRUE is equal to 1 and FALSE is to zero. Thus if both
($I$4>=$Q4) and ($I$4<=$R4)
are TRUE their multiplication returns 1 (other words TRUE). If at least one is FALSE the result is 0 (or FALSE). That is the equivalent of AND() function.
