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
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.

Tech Helproom


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


 

excel formula not quite right


c0mpy

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

Like this post
lotvic

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.

Like this post
c0mpy

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

Like this post
lotvic

Likes # 0

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

Like this post
lotvic

Likes # 0

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

(I was using row 28)

Like this post
natdoor

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.

Like this post
natdoor

Likes # 0

Sorry lotvic, I did not see your posts.

Like this post
lotvic

Likes # 0

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

Like this post

Reply to this topic

This thread has been locked.



IDG UK Sites

iPhone 6 vs Samsung Galaxy S5 comparison review: Apple takes on Samsung once again in smartphone...

IDG UK Sites

Just another opinion about Apple's new iPhone

IDG UK Sites

Intel Xeon E5 v3 Haswell processors review: we check out the fastest chips on the planet

IDG UK Sites

Apple Watch hands-on review | Apple Watch design, spec, features & UK pricing