Max/min

[dodokh]

Hello,

Can you help me to analyze the formula in cell G4 please

https://www.dropbox.com/s/zodfwf08ima5w16/Excel.jpg?dl=0

Thank in advance

 

[JLGWhiz]

this is how I read it

A. maximum value of
1. Either zero or
2. the result of the mnimum value between H3 value and (B4 value + 1) minus the maximum value between G3 value and (D4 value + 1)
B. Divided by
1. the result of E4 value minus ((D4 value + 1) times F4 value)

But I would have to put some numbers in the ranges and check the outcome to be sure.

 

[Truiz]

I'll try my best...

First things first... You need to understand how dates work in excel.

When you input a date in excel that date has a "Value" for example if you type 01/01/2008 (January first two thousand and eight) Excel recognizes it as a date if you change the format to number instead of displaying 01/01/2008 you'll see the number 39448. I know it's a bit strange and confusing but it kinda makes sense, see excel starts counting time since 01/01/1900 so 39448 simply means the amount of days that have gone by since 01/01/2008 until 01/01/2008 in other words 01/01/1900 = 1 01/02/1900 = 2 and so on... having said that lets take a look at the formula

Know that we have established that dates have a "Number Value" we can see whats going on the let take a look at the first part

MAX(0,MIN(H$3,$E4+1)-MAX(G$3,$D4))

This basically states we want excel to return the greatest value between 0 and some other number that is determined with other formulas lets look at it this way the greatest number between 0 and "A"

A = MIN(H$3,$E4+1)-MAX(G$3,$D4) we can change this to A = B - C
B = MIN(H$3,$E4+1)... in your case the cell H3 contains the date Feb-08 which could be any day of that month just formatted to read Feb-08 but I'm going to assume its Feb the first that date is equals 39479 E4 is 12/31/2008 (December 31st) which equals 39813 so basically the smallest number between 39479 and 39814 (because of the +1 in the formula) the result is 39479 so B = 39479

C = MAX(G$3,$D4)... the biggest number between the 2 numbers G3 = 01/01/2008 or 39448 D4 = 01/01/2008 or 39448 since both numbers are the same the answer is 39448

Now A = B - C is equals A = 39479 - 39448 A = 31

Going back to our

MAX(0,MIN(H$3,$E4+1)-MAX(G$3,$D4))

now we know we are looking for the biggest number between 0 and A A=31 the result then is 31

Lets take a look at the other side

($E4-$D4+1)

E4 = 39813
D4 = 39448

So E4-D4 +1 = 366

now 31/366 = 0.084 (Roughly)

and 0.084 * F4 (Since F4 = 252,000) = 21,344 (Rounded with no decimals)

Hope that makes sense

 

[Truiz]

If what you were wondering was what the formula "means" or the purpose of it, what the formula is doing is simply distributing the budget per month depending on the amount of days each month has, there are other ways (Perhaps easier to read) for the task to be accomplished for example

=(DAY(EOMONTH(G3,0))*F4)/(365+IF(MOD(G3,4),0,1))

Which is basically the amount of days in the month times the budget divided by the amount of days in the year

 

[dodokh]

Thanks all

 

[barry houdini]

.......there are other ways (Perhaps easier to read) for the task to be accomplished for example

=(DAY(EOMONTH(G3,0))*F4)/(365+IF(MOD(G3,4),0,1))

Which is basically the amount of days in the month times the budget divided by the amount of days in the year

This is OK if the distribution period is a whole year, but the original formula is "clever" enough to be able to distribute the given amount over the months even where there are partial months, e.g. if the "starting date" in D4 is changed to 15-Feb-08 and the end date in E4 to 20-Nov-08 then the results using the original formula will be zero in January and December and the correct pro-rata amounts in February and November