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

View Forum Rules | Contact Forum Editor

 

excel formula not quite right

c0mpy

Likes # 0

Posted November 10, 2011 at 11:34PM

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

Posted November 11, 2011 at 9:29AM

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

Posted November 11, 2011 at 1:03PM

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

Posted November 11, 2011 at 2:46PM

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

Like this post
lotvic

Likes # 0

Posted November 11, 2011 at 2:48PM

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

(I was using row 28)

Like this post
natdoor

Likes # 0

Posted November 11, 2011 at 3:10PM

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

Posted November 11, 2011 at 3:12PM

Sorry lotvic, I did not see your posts.

Like this post
lotvic

Likes # 0

Posted November 11, 2011 at 3:38PM

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

Like this post

Reply to this topic

This thread has been locked.



Check out PC Advisor's other tech forums

Send to a friend

Email this article to a friend or colleague:

PLEASE NOTE: Your name is used only to let the recipient know who sent the story. Both your name and the recipient's name and address will not be used for any other purpose.