IF Function 2

[lisa02]

Nearly Free Internet
Limit Hours Free Internet(2),Fee per hour($1.20),MaxFee ($8.00)


Date Entery Exit Hours Fee Free Connection
Time Time or Fee
Cost
11/11/2006 8 40 8:40 a.m. 13 40 1:40 p.m. 5:00 5 Cost
12/11/2006 10 41 10:41 a.m. 12 15 12:15 p.m. 1:34 1 Free
13/11/2006 11 13 11:13 a.m. 12 30 12:30 p.m. 1:17 1 Free
14/11/2006 8 42 8:42 a.m. 15 45 3:45 p.m. 7:03 7 Cost
15/11/2006 8 43 8:43 a.m. 17 36 5:36 p.m. 8:53 8 Cost
16/11/2006 8 43 8:43 a.m. 18 10 6:10 p.m. 9:27 9 Cost
17/11/2006 8 43 8:43 a.m. 13 10 1:10 p.m. 4:27 4 Cost
18/11/2006 8 44 8:44 a.m. 14 22 2:22 p.m. 5:38 5 Cost
19/11/2006 10 46 10:46 a.m. 11 7 11:07 a.m. 0:21 0 Free
20/11/2006 11 15 11:15 a.m. 13 26 1:26 p.m. 2:11 2 Free
21/11/2006 8 47 8:47 a.m. 17 18 5:18 p.m. 8:31 8 Cost
22/11/2006 8 50 8:50 a.m. 17 43 5:43 p.m. 8:53 8 Cost

Above is a exercise that i have been working on as apart of my study of excel and i am stuck on the IF function part of it. under Connection Fee i need to use a IF function to work out how much it will cost the internet user. the cost is $1.20 perhour but the first 2 hours are free, and the max fee is $8.00. can you please show me how to do this. each one should have either a value that is $8.00 or under and the ones that are Free should be 0. the main problem that i am having is that i can not get the 9 hour one to read just $8.00 it comes up as $8.40.

 

[joerd]

The IF function is really straightforward and logical. The setup is easy: IF ( (condition), (what to do when condition is TRUE), (what to do when condition is FALSE) ). What you put into the (condition) part is important, it has to be a logical expression, such as (A1 < 8) or (B2 = 9). Then, if A1 is less than eight, the condition will be TRUE, and if A1 is equal to 8 or greater, then the condition is false. Likewise, if B2 is exactly equal to 9, the second condition is true, otherwise it is false. If you post the IF statement you came up with so far, we can help you debug it.
Another function you may find useful are the MAX and MIN functions, which give the highest resp. lowest value in their argument list. So, MIN(12, 8, 24) returns 8.

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[lisa02]

=IF((J13-$D$2)>$D$2,$H$2*(J13-$D$2))

which is =IF((9-2)>2,$1.20*(9-2)) =$8.40
this gives me the total cost but i dont know where, and how to add in the, max fee of $8.00

 

[MintJulep]

=min(=IF((J13-$D$2)>$D$2,$H$2*(J13-$D$2)),8)

 

[MintJulep]

Sorry, don't need the = for the if anymore

=min(IF((J13-$D$2)>$D$2,$H$2*(J13-$D$2)),8)

 

[dik]

Thanks, MintJulep... and to finish this, if you set a variable for the max cost and reference it rather than hardcode the '8' into the function... I dislike hardcoding values... Add a 'one liner' at the top... Maximum fee = (some cell). Based on recent discussions in the spreadsheet forum, I've started using cell value variables more and more...

Dik

 

[MintJulep]

Then just replace the 8 with the cell reference, or preferably, use a range name.