# excel formula not quite right

c0mpy 23:34 PM 10 Nov 11
Locked

Hello,

After a number of errors on my EON electricity bill, I have been checking in case anything else wrong. I’ve entered all my usage in an excel spread sheet. I have a slight problem with Economy7 which has a split day rate. Here is formula and how I entered it in excel. Everything works except final cost 6.00 is incorrect, it should be 6.08 not 6.00

Economy 7 Day rate formula for First unit threshold is: 900kWh ÷ 365 (days in a year) x number of days in billing

Cells: B C D E F G H
Row 26: (8) (5156) (5220) (640) (20) (0.304) (6.00)

(separates cell contents)

All figures from row 26

Cell F contains formula =900/365*B26 (20 is correct kWh at 1st rate)

Cell H contains formula =G26*F26

Cost in H should be 6.08 why is it not???

Some simple but annoying glitch I think.

Can anyone help?

Hexie

lotvic 09:29 AM 11 Nov 11

Probably the number of decimal places used in calculations and/or the ROUNDUP or ROUNDDOWN. It's practically impossible to guess which/where EON apply these.

c0mpy 13:03 PM 11 Nov 11

Thanks for your thoughts but it is not rounding up/down.

EON have correct figure 6.08 on bill. What's wrong is how excel is dealing with figures when I enter them into cells as per my earlier post. The answer comes out wrong at 6.00> I tried roundup/down/down but 6.08 it is not.

Small difference but over a long term figures get out of sync with bill.

It must have something to do the way I entered formula into cells or the fact that I am using cells that contain formula in a further calculation.

Can anyone with excel expertise advise.

Hexie

lotvic 14:46 PM 11 Nov 11

I think F26 should be =ROUNDUP(900/365*B28,0)

lotvic 14:48 PM 11 Nov 11

Sorry, F26 should be =ROUNDUP(900/365*B26,0)

(I was using row 28)

natdoor 15:10 PM 11 Nov 11

If I understand correctly, you are expecting the formula in F to produce a value of 20 which, when multiplied by the contents of G to produce the result 6.08. If the result of the formula in F were really 20, then of course this would be so. However, 900*8/365 actually equals 19.7260... which should produce a cost in H of 5.996...

So it seems that rounding needs to be applied to cell F and cell H.

natdoor 15:12 PM 11 Nov 11

Sorry lotvic, I did not see your posts.

lotvic 15:38 PM 11 Nov 11

It's okay natdoor, good to have it confirmed, thanks.