excel formula not quite right

  c0mpy 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
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?

Hexie

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

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

  lotvic 11 Nov 11

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

(I was using row 28)

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

Sorry lotvic, I did not see your posts.

  lotvic 11 Nov 11

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

Advertisement

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

Should I upgrade to Windows 10? 8 reasons why you should upgrade to Windows 10... and 2 why you…

We are being sold the ability to spend money we don't have. And we love it

See 24 hours in London with Paul Richardson's hyperlapse tour

How to use Apple Music in the UK: Complete guide to Apple Music's features

We use cookies to provide you with a better experience. If you continue to use this site, we'll assume you're happy with this. Alternatively, click here to find out how to manage these cookies

hide cookie message