excel formula not quite right

  c0mpy 23:34 10 Nov 2011


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 11 Nov 2011

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 11 Nov 2011

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 11 Nov 2011

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

  lotvic 14:48 11 Nov 2011

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

(I was using row 28)

  natdoor 15:10 11 Nov 2011

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 11 Nov 2011

Sorry lotvic, I did not see your posts.

  lotvic 15:38 11 Nov 2011

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

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

Elsewhere on IDG sites

iPhone X review

Political cartoons in 2017: Chris Riddell, Rebecca Hendin and Dave Brown on what it’s like to…

The best iPhone for 2017

Tennis : comment regarder la finale de la Coupe Davis 2017 ?