×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

IF Function
2

IF Function

IF Function

(OP)
                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.   

RE: IF Function

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.

RE: IF Function

(OP)
=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

RE: IF Function

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

RE: IF Function

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

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

RE: IF Function

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

RE: IF Function

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources