Contact Forum Editor

Send an email to our Forum Editor:

PLEASE NOTE: Your name is used only to let the Forum Editor know who sent the message. Both your name and email address will not be used for any other purpose.

# Beginners' Tech Help

It's free to register, to post a question or to start / join a discussion

# excel formula not quite right

Likes # 0

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

Likes # 0

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.

Likes # 0

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

Likes # 0

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

Likes # 0

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

(I was using row 28)

Likes # 0

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.

Likes # 0

Sorry lotvic, I did not see your posts.

Likes # 0

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