# Would I use an =if function?

#### Brendan

##### Board Regular
I dont really know how to explain this one, but I will try my best to convey the problem I have.

I have a phone plan that gives me 120 minutes for a month.
100 minutes can only be used after 7pm, and 20 can be used whenever.
I have an entry into how many offpeak minutes (b4) and how many peak minutes(b5) I think I will use in a month.

so what I want to say is if I use 20 offpeak minutes (I am given 20 free) for it not to read as a zero.
If I use anything up to 120 offpeak minutes, the end should still read zero, but if I use 21 peak minutes, the result should read 1.
Or another scenario would be 19 peak minutes, and 101 offpeak minutes to also read zero (max limit)

Im thinking it would be a complex =if function, but not really 100% sure, as I have been playng around with this for a while.

What I have worked out so far is that I should be using an if function (maybe I am wrong) ..
Im not sure if I have conveyed my problem too well so if you need any further explanation, please ask :P

Assistance would be appreciated, because Im trying to work out a calculator for me and my friends to find out what plan I would be saving the most money on ...

Cheers.

### Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
how are you storing the amount you've actually used?

Im not sure if your meaning in B4 and B5.
If your not, Im not storing them anywhere else.

This is a blatently incorrect calculation, but Im thinking im half way there with it ..

=IF(B4+B5>120,IF(B5>20,B5-20,0),0)

Just cant see where Im going overly wrong with it ...

"so what I want to say is if I use 20 offpeak minutes (I am given 20 free) for it not to read as a zero.
If I use anything up to 120 offpeak minutes, the end should still read zero, but if I use 21 peak minutes, the result should read 1.
Or another scenario would be 19 peak minutes, and 101 offpeak minutes to also read zero (max limit)"

...you meant:

so what I want to say is if I use 20 offpeak minutes (I am given 20 free) for it to read as a zero.
If I use anything up to 120 offpeak minutes, the end should still read zero, but if I use 21 peak minutes, the result should read 1.
Or another scenario would be 19 peak minutes, and 101 offpeak minutes to also read zero (max limit)

... then:

=IF(B4<=20,0,B4-20)

...because you've given no indication of what you wish to happen when the offpeak (also) exceeds the threshold.

Frankly, think your set-up's a bit to simple - from what I understand, you need to assess:

total minutes
off-peak portion allowance & cost
peak portion allowance & cost
whether peak overrun can be absorbed in unused offpeak minutes & at what rate
whether offpeak can be absorbed in unused peak minutes & at what rate
pentaly rates & tiers for overruns

...sound about right, or am I overcomplicating things?

Hi, Brendan
Welcome to the Board !!!

this was my first result
A        B       C
1          offpeak anytime
2 included 100     20
3          offpeak peak
4 spent    105     30
5 overuse  5       10
6
7 result   15

2005

[Table-It] version 06 by Erik Van Geit
Code:
``````RANGE FORMULA (1st cell)
B5    =IF(C5<0,B4-B2+C5,B4-B2)
B7    =MAX(0,B5)+MAX(0,C5)
C5    =C4-C2

[Table-It] version 06 by Erik Van Geit``````
second: the formulas can be "condensed" to
Code:
``````RANGE FORMULA (1st cell)
B7    =MAX(0,IF(C4-C2<0,B4-B2+C4-C2,B4-B2))+MAX(0,C4-C2)

[Table-It] version 06 by Erik Van Geit``````

hoping to be on the right track

kind regards,
Erik

1 offpeak anytime
2 included 100 20
3 offpeak peak
4 spent 105 30

I have this currently splattered over multiple sheets.

I guess I need to have an overuse column somewhere aswell to simplify things.
Cheers for that, Ill give this a go and let you know how it turns out.

Ideally, I would love to send the sheet to someone so they can actually see what I mean, as ive done a fair amount of work on it, and I know its just some small area where I am going wrong .. haha.

Thanks for ya help.

A B C
1 offpeak anytime
2 included 100 20
3 offpeak peak
4 spent 105 30
5 overuse 5 10
6
7 result 15

I encountered a problem with this.
Depending on how the peak minutes are used, (if none are used at peak at all) off peak can range from 100 - 120, and the over use should still return as a zero.
This was where I was getting myself confused originally.
Is there anyway for me to say to excel
'If Peak is less than 20, allow offpeak to absorb the excess'
?

I have this currently splattered over multiple sheets.
first forget about your project & try to make this little thing work
when it's OK, then cut and paste to the correct sheets/cells

I encountered a problem with this.
Depending on how the peak minutes are used, (if none are used at peak at all) off peak can range from 100 - 120, and the over use should still return as a zero.
This was where I was getting myself confused originally.
Is there anyway for me to say to excel
'If Peak is less than 20, allow offpeak to absorb the excess'
to my sense the formulas do exactly what you mean
provide an example of what would not be correct

200 peak 0 offpeak
result 80 (instead of 100, because 20 anytime minutes are used)
A        B       C
1          offpeak anytime
2 included 100     20
3          offpeak peak
4 spent    200     0
5
6
7 result   80

phone

[Table-It] version 06 by Erik Van Geit
Code:
``````RANGE FORMULA (1st cell)
B7    =MAX(0,IF(C4-C2<0,B4-B2+C4-C2,B4-B2))+MAX(0,C4-C2)

[Table-It] version 06 by Erik Van Geit``````

greetings,
Erik

another table to try out easily all scenarios
A        B       C       D    E
1 included         spent
2 offpeak  anytime offpeak peak result
3 100      20      0       10   0
4                  0       25   5
5                  80      20   0
6                  80      25   5
7                  80      30   10
8                  100     20   0
9                  100     25   5
10                  100     30   10
11                  120     0    0
12                  120     10   10
13                  120     20   20
14                  150     20   50
15                  150     25   55
16                  150     30   60

phone

[Table-It] version 06 by Erik Van Geit
Code:
``````RANGE   FORMULA (1st cell)
E3:E16  =MAX(0,IF(D3-\$B\$3<0,C3-\$A\$3+D3-\$B\$3,C3-\$A\$3))+MAX(0,D3-\$B\$3)

[Table-It] version 06 by Erik Van Geit``````

Replies
3
Views
794
Replies
10
Views
254
Replies
3
Views
220
Replies
1
Views
537
Replies
3
Views
243

1,219,960
Messages
6,151,164
Members
451,012
Latest member
OH650R

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back