Yipppeeee !! this is what i exactly wanted.. Thanks a million Mr. Biff !!!
I wish i know excel like you do
Also can you explain me how this formula works.. i managed to figure out till
IF(CHOOSE({1,2,3,4},A2,A4,B3,B6)>=TODAY(),CHOOSE({1,2,3,4},A2,A4,B3,B6)
But dint understand the IFERROR(1/(1/MIN
Please help me understand so that i can use this formula some time in the future if reqd.
Cheers,
Prash
Try this experiment.
Let's assume cell B6 contains the date 10/31/2012 and the formula returns that date as the result.
In some cell enter this formula:
=1/B6
Format this cell as Number and take it out to 15 decimal places. You will see this number: 0.000024264188484
In Excel, dates are really just numbers formatted to look like dates. These numbers are commonly referred to as the date serial number. The date serial nuimber is the count of days starting from a base date. The default base date is January 1, 1900. So, January 1, 1900 is date serial number 1.
Jan 1 1900 = date serial number 1
Jan 2 1900 = date serial number 2
Jan 3 1900 = date serial number 3
Jan 4 1900 = date serial number 4
Jan 5 1900 = date serial number 5
etc
Jan 1 1975 = date serial number 27395
Jan 1 2000 = date serial number 36526
Oct 31 2012 = date serial number 41213
You can see the date serial number by entering some date in a cell and then changing that cells format to General or Number.
OK, back to our experiment.
=1/B6
So, what we're doing is dividing 1 by the date serial number 41213:
=1/41213 = 0.000024264188484
Now, if we divide 1 by that number:
=1/0.000024264188484 = 41231
We can say that 1/(1/N) = N except when N=0.
So, if B6 contians the date 10/31/2012 (serial number 41213), then:
=1/(1/B6) = 41213 (date 10/31/2012)
However!
When there are no dates that meet the condition then the MIN function will return 0 which we don't want.
So, we get this:
1/0 = #DIV/0!
1/(1/0) = #DIV/0!
Then the IFFERROR function traps that #DIV/0! error and returns blank.
