We help IT Professionals succeed at work.

# formula check

on
Medium Priority
447 Views

Can someone please tell me what is wrong with this formula?

select (200 * (31- datepart (d,( getdate()))/31)
+ 200 + 200 +
datepart (d,( getdate()))/31*200
) /31 as MonthlyForecast

I get a return of 212.  I was told the value should not be more than 200
using Lever law to get an average over 4 months.
Comment
Watch Question

## View Solution Only

CERTIFIED EXPERT
Awarded 2010
Top Expert 2013

Commented:
(31- datepart (d,( getdate()))/31)

That part has a problem. You divide the day of the month by 31 (which gives a value from 0 to 1) then you subtract it from 31 giving you a number always from 30 to 31.
Without having any idea what you are really trying to do, I would guess that should be
(1- datepart (d,( getdate()))/31)
So it's still scaled from 0 to 1.
CERTIFIED EXPERT
Awarded 2010
Top Expert 2013

Commented:
Also, what's with the +200+200+? Why not just do +400+? Should one of those be a *?

Commented:

For clarity putting d,( getdate())) = Y

you get

(200 * (31- Y/31)+ 200 + 200 +Y/31*200) /31

=(31*200-200*Y/31 +400+200*Y/31)/31

=(31*200+400)/31

= ((31 * 200) + 400) / 31 = 212.903226

ie it is always the same.

are you sure Y/31*200 should not be Y/(31*200)?
CERTIFIED EXPERT
Awarded 2010
Top Expert 2013

Commented:
If you could explain the formula in English, we can help you get it into code.

Commented:
thank you all.
Basically what we are trying to do is get an average of monthly forecast data.
for Item X the forecast is 200 this month, 200 for May, 200 June, 200 July. Instead of doing an average of the 4 values,  We want to use April's base on the current day and the same for the 4th months.

an other words weight of April is base on where we are in the month.  Level Law
CERTIFIED EXPERT
Awarded 2010
Top Expert 2013

Commented:
So let's say today is the 26th. What should the value be?

( 200*(26/30) + 600 )/4 ? That doesn't really make sense. I don't know what you mean by 'Level Law'

Commented:
no.
The formula I was given is:

X = [ 300 [ 31-today /31] + 400 + [today/31] 200 ] / 31

when I put that in in code I get 212, but I was told that is incorrect
CERTIFIED EXPERT
Awarded 2010
Top Expert 2013

Commented:
Again 31 - today/31 cannot be right. It must mean 1 -  today/31 or (31-today)/31 because that gives a ratio representing the percent of the month that remains.
Dividing the whole thing by 31 again doesn't make sense either. Should that be a 4?
I think they may have meant
X = [ 200 [1-today/31] + 400 + 200 [today/31] ] / 4
But that still doesn't make sense because it will give you 150 every time since 1-today/31 ... + today/31 = 1

Also, what if a month has 30 days? (or 28 or 29)
CERTIFIED EXPERT
Awarded 2010
Top Expert 2013

Commented:
Do they just want the projection for what has passed so far?
I propose
200*4*(datediff(d, '4/1/2011',getdate())/datediff(d, '4/1/2011, '8/1/2011'))

That will give the percent of the total projection that you should have seen so far. Or you might want the total projection remaining:
200*4*(datediff(d, getdate(), '8/1/2011')/datediff(d, '4/1/2011, '8/1/2011'))
CERTIFIED EXPERT
Awarded 2010
Top Expert 2013

Commented:
Notice how that works no matter how many days are in the month. I think that may be what they are after.

Commented:
Hey Tommy,

I just got a little more clarification.
What we are trying to get is the percentage of the remainder of days for the current month and the number of days difference for the last month.

so today is the 26. 4days left: so we want fraction of 4/31 * the first value + 200 +200 +  26/31 of the last
CERTIFIED EXPERT
Awarded 2010
Top Expert 2013

Commented:
But (as Gwyn mentioned earlier) x/31 + (31-x)/31 = 1 always. So you will always get 300 every time.
Also you still have the problem of some months not having 31 days. Whoever is telling you to do this is either not explaining it well or is confused.

What is that number supposed to accoplish? I still stick by my recommendation of looking at the percent of time that has passed for the whole period.

Commented:
should this

X = [ 300 [ 31-today /31] + 400 + [today/31] 200 ] / 31

be

X = [ 300*[ (31-today) /31] + 400 +  200*[today/31]] / 31

CERTIFIED EXPERT
Awarded 2010
Top Expert 2013

Commented:
No

Commented:
Tommy,

"Do they just want the projection for what has passed so far?"
no.
We have a software that is forecasting inventory for the next 4 months including the current month. We are extracting an Average of those 4 months for a  report, but instead of getting a straight average,  we want a fraction of the current month base on days left plus the forecast number for the 2 months in the middle and a fraction of the last month Divided by 3.

--
The thing is the person that is explaining this is doing on paper, so when they do remaining days of the month divided by 31 (i.e. 3/31), they get a decimal number and you do 200 times that decimal number  + 400 + decimal value of 27/31  time 200 Then divide all of it by 3.
That is the peace I was missing  I was originally dividing by 31.

CERTIFIED EXPERT
Awarded 2010
Top Expert 2013

Commented:
Again 200*(3/31) + 200*(27/31) = 200*(30/31)
Or      200*(10/31) + 200*(21/31) = 200*(30/31)

So no matter what day it is, the answer will always be (200 + 200 + 200*30/31)/3.

Show this result to the person giving you the formula and explain that it makes no sense. Why in the world is the last month a fraction? Shouldn't the only fraction be this month?

Commented:
Any suggestion on how to achieve what i'm looking for?
CERTIFIED EXPERT
Awarded 2010
Top Expert 2013

Commented:
If you use 200 as the base and then get a fraction of the 200 and weight that with the same fraction, then you'll always end up with 200/mo

Are you trying to change the projection based on what you have so far this month? Like, let's say the month is half over and you already have 130, should the average be slightly higher than the 200? Like perhaps we want 130 + 100 projected remaining + 3*200 for the other months = 830/8 = 207.50 per month?

Or if the month is half over do you want 700 total projected remaining? I'm not sure what you are looking for so it's hard to suggest how to achieve it. If you are doing projections, then it makes sense to modify those projections based on the real data so that's what I would suggest.

Commented:
I don't want a projection. I just want to get an Average of those 4 months forecast values.
CERTIFIED EXPERT
Awarded 2010
Top Expert 2013

Commented:
The average is 200. Always. If each month has a forecast of 200, then the forcast for 1/2 of a month is 1/2*200 so the forcast for each month is 200.

The forcast for 3.5 months is 200*3.5 so if you want total remaining forcast, you could use my earlier formula to get the percentage of days remaining, but the average forcast will always be 200/mo.

Commented:

ok. what if we had 300 month 1, 400 month 2, 600 month 3 and 100 month 4.
CERTIFIED EXPERT
Awarded 2010
Top Expert 2013

Commented:
(300+400+600+100)/4 = 350
Halfway through month 1 remainder is (150+400+600+100)/3.5 = 357.14
Is that what you are going for?

Commented:

Yes That's it.  What if i'm 10 or 20 days into month 1?
CERTIFIED EXPERT
Awarded 2010
Top Expert 2013

Commented:
(300*datepart(d, getdate())/31 + 400 + 600 + 100)/(3 + datepart(d, getdate())/31) (which is the same as [300*10/31 + 400 + 600 + 100]/[3+10/31] if you are 10 days in.
If you are fine with assuming 31 days. Otherwise you'll need something complicated to calculate the total days in the month. You could make a function; the code is out there, just Google for it.

Or you could multiply the total by the total percent of time not yet passed like:
(datediff(d, getdate(), '8/1/2011')/datediff(d, '4/1/2011, '8/1/2011'))*(300+400+600+100)

Commented:
base on below if I have 200 for each month shouldn't I get 200. instead I get 258
"(300*datepart(d, getdate())/31 + 400 + 600 + 100)/(3 + datepart(d, getdate())/31) "

Commented:
this (datediff(d, getdate(), '8/1/2011')/datediff(d, '4/1/2011, '8/1/2011'))*(300+400+600+100)
gives me 0
CERTIFIED EXPERT
Awarded 2010
Top Expert 2013

Commented:
I guess you need to cast it to a double. It must be doing integer division

(200*datepart(d, getdate())/31 + 200 + 200 + 200)/(3 + datepart(d, getdate())/31)
=(200*27/31 + 600)/(3 + 27/31) = 774/3.87 = 200
Are you sure you didn't leave a 300 or something?

Commented:
Do you mean that
(200*27/31 + 200 + 200 + 200)/(3 + 27/31)
gives something grossly different than 200?

Commented:
I got what I wanted by doing the following:

((((31- datepart (d,( getdate())))*1.0/31) *200)+200+200+ (((datepart (d,( getdate())))*1.0/31) *200) )/3

but i'm getting one corky thing that's throwing me for a loop.
((((31- datepart (d,( getdate())))*1.0/31) *150)+0+0+ (((datepart (d,( getdate())))*1.0/31) *150) )/3

is giving me 100 when I used the values that is in the sql fields. ???
CERTIFIED EXPERT
Awarded 2010
Top Expert 2013
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)

Commented:
I'm seeing a pattern. i only get that discrepancy when there's a zero in one of the two middle months values.  it works for 98% of the line  items except for when there's a zero in

When I key in the values in the formula I mentioned I do get 49.99.
i.e.  ((((31- datepart (d,( getdate())))*1.0/31) *200)+200+200+ (((datepart (d,( getdate())))*1.0/31) *200) )/3

Commented:
sorry I meant:

i.e.
Select  ((((31- datepart (d,( getdate())))*1.0/31) *150)+0+0+ (((datepart (d,( getdate())))*1.0/31) *150) )/3
= 49.99

Commented:
got it.  Copy and paste error

Commented:
Thanks Tommy.

I will award you the points for helping me get to this point and your constant feedback.

solution:
((((31- datepart (d,( getdate())))*1.0/31) *200)+200+200+ (((datepart (d,( getdate())))*1.0/31) *200) )/3
Unlock the solution to this question.