Calculate variances

[Climaxgp]

Hi Guys

Just wanted to find out if the formulas in the attached apreadsheet are correct. Formulas from E6 to E10.

Also, if you multiply the "new daily target" with "working days" should'nt this give you the "remaining" figure? Currently it's not doing this... i think.

[Climaxgp]

Anyone..?

[zbor]

As I see it:

E7-SUM(F16:F175)/(E9-COUNT(F16:F175))
E7= E5-E6
E6=SUM(F16:F175)

So first fomula is actually:

(E5-E6-E6)/(E9-countF)= E5/(E9-countF)

E9 is number of working days... count will give you how many days you've already worked

So New daily target is average of target in remaining days (only nicer written )
And I think it doesn't make much sence....

It should be E7/(E9-countF) right?

[DonkeyOte]

Thanks for revising title - good titles aid the search engine.

[zbor]

Congratulations on moderator title

[Climaxgp]

@ DonkeyOte, no need to thank me, it was my fauly anyway!

Thanks for your prompt reply to my problem. I know i'm being stupid here, but i don't understand what you telling me with regards to your solution

[zbor]

I'm telling is =E7/(E9-countF) correct formula for E10?
And do you need anything more or this is it?

[Climaxgp]

Hi Zbor,

Thank you.

I did use the formula you gave me in E10. But when i calulate "New Daily Target" X "working days" it still does not give me the correct "remaining" target.

[zbor]

Because you multiply constant 160 (all days) with New Daily target.
You must multiply remaining days, not all.

But then you will run in circle

=E7/(E9-countF) * (E9-countF)
or it's same as writing =E7

Also, take my advice and don't merge cells in excel

[Climaxgp]

But i do multiply the remaining days and it still does'nt work...

Wonder why?

[zbor]

Let me see am I getting this right:

if you multiply the "new daily target" with "working days" should'nt this give you the "remaining" figure?

You get new daily target (E10) as remaining (E7) divided by working days (E9) minus number of worked days (CountifF16:F175)

Now, if you multiply that number by Working days (E9) you will get Remaing (E7) back again only in case that countif is 0.

This is your situation:
A=B/(C+D)

If you mutliply that result with C you can't get A back because D is <>0

[Climaxgp]

Hey Guys

How can i resolve this problem with the variances in calculations? I'm at my wits end with this