excel formula not quite right

  c0mpy 23:34 PM 10 Nov 11


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
Headings:(Days) (old-kWh0 (new-kWh0)(diff) (1st)(rate) (cost)
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?


  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.


  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.


This thread is now locked and can not be replied to.

How to watch iPhone 6s, iPhone 6s Plus launch live: What to expect from Apple's 9 September 2015…

1995-2015: How technology has changed the world in 20 years

This installation lets you control a laser and sound show using your mind

Apple's special event invitations decoded: A look back at 13 of Apple's most cryptic invites