Would I use an =if function?

Brendan

Board Regular
Joined
Aug 31, 2006
Messages
51
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

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Brendan

Board Regular
Joined
Aug 31, 2006
Messages
51
Im not sure if your meaning in B4 and B5.
If your not, Im not storing them anywhere else.
 

Brendan

Board Regular
Joined
Aug 31, 2006
Messages
51
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 ...
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234

ADVERTISEMENT

assuming that instead of:

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

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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
 

Brendan

Board Regular
Joined
Aug 31, 2006
Messages
51

ADVERTISEMENT

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.
 

Brendan

Board Regular
Joined
Aug 31, 2006
Messages
51
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'
?
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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
 

Forum statistics

Threads
1,136,702
Messages
5,677,290
Members
419,684
Latest member
BOB101

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top