need help with formula

[myrchik]

Hello guys...i am having a trouble of creating one formula, and there fore need some help over here.

So, i have a SUM of a many cells and it works great, however it works only if the value in one particular cell is over 100, if the value in that cell is less than a 100 than the entire formula needs to be changed.

So my question is how do i make so that excel would change the formula from one to the other if tha value is below or above 100....something like IFTTT, if the value 100 then use this formula, and if the value is less than 100, then use this formula?

I need this because i am trying to keep track of my utility bills and HYDRO meter has two zones, day and night. Plus on top of that first 100KWa is charged for 0.456/KWa, and everything above 100 is 0.789/KWa.

so the formula is a bit complex if you have more than 100 KWa/month, but if you have under 100 KWa/month than it is very simple....so i can't find solution of how to set my excell up so that it would know what formula to use if i enter lets say 80, or if i enter 120 as a total KWa/month.

Lets say for example:
for September 2015 i used 120 kwa total, 70 during the day, and 50 during the night + first 100 KWa charged at 0.456/kwa, and everything on top of 100, in our case it is 20 is charged at 0.789/kwa. Also, the night rate is 50% off.
So i made this formula:

Total 120
day 70
night 50

=SUM(70/120)*100*0.456+
=SUM(50/120)*100*0.456*0.5+
=SUM(70/120)*20*0.789+
=SUM(50/120)*20*0.789*0.5=

So, i need to use above formula if the monthly value is over 100KWa, and it works great....however, if the monthly amount is less than 100KWa, lets say 99 than it doesn't work because the formula has to be different, it should be:
Total 99
dau 50
night 40

=SUM(50*0.456)+
=SUM(40*0.456*0.5)=

So, in my set up this two formulas depend only upon one cell value....so how do i make it right...is there a way to write such a formula or command in a SUM cell so that it would know what formula to use in order to calculate what i need.


Thank you very much in advance!

[FDibbins]

Hi, welcome to the forum

Please take a moment to read the forum rules and then amend your thread title to something descriptive of your problem - not what you think the answer might be. (think google search terms?). Once you have done this please send me a PM and I will remove this request. (Also, include a link to your thread - copy from the address bar)

Many members search our previous posts, and thread titles play a big part of the search. I doubt anybody would do a search based on your title?

To change a Title on your post, click EDIT POST then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(note: this change is not optional )