Hi all,
I am using an if function to check a range of cells with dates, compare them to todays date, and if it matches todays date, to return the value of the cell above.
So far I have the following formula working:
=MIN(IF(J2:AF2=TODAY(), J1:AF1))
It matches the cell with 03/03/2021 to todays date therefore If Function returns TRUE, and returns the value of the cell above, being 3.
Capture1.JPG
However, I wanted to expand on this for other months, as if I pasted that formula in the table of dates for February it realises that none of those dates matches today, and returns a 0. So, for months in the past, I want it to return the largest number in the range, and for months in the future, I want it to return zero.
And so I actually got this formula working: (Gives me 20 for February, and 0 for April.
=MIN(IF(J2:AF2=TODAY(),J1:AF1,IF(J2:AF2>TODAY(),0,(MAX(J1:AF1)))))
Capture2.JPG
Capture3.JPG
This formula returns correct values for months in the past and months in the future, BUT, it now doesn't return the correct value for the current month - it is giving me 0 instead of 3. So its like its saying that the first if function is no longer returning TRUE, and so goes IF FALSE, and does the next if function...
Capture4.JPG
What have I done wrong??
Thanks for any help!!
Bookmarks