Results 1 to 9 of 9

Thread: My If statement

  1. #1
    Join Date
    Feb 2004
    Location
    Vermont
    Posts
    53

    Question Unanswered: My If statement

    My if statment is as follows, but I keep getting an error message. Can you tell me what I am doing wrong?

    =If(D14<45), ((D14-30)*75 + 45), If(AND(D14>45)(D14<120), ((D14-60)*75) + 65, If(D14>120),((D14-120)*75)+125
    CAT :-D

  2. #2
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067

    Re: My If statement

    Originally posted by carley465
    My if statment is as follows, but I keep getting an error message. Can you tell me what I am doing wrong?

    =If(D14<45), ((D14-30)*75 + 45), If(AND(D14>45)(D14<120), ((D14-60)*75) + 65, If(D14>120),((D14-120)*75)+125
    Hi

    This Formula should work for you.

    =IF(D14<45,((D14-30)*75+45),IF(AND(D14>45,D14<120),((D14-60)*75) + 65,((D14-120)*75 +125)))


    David

  3. #3
    Join Date
    Feb 2004
    Location
    Vermont
    Posts
    53

    Re: My If statement

    This didn't work either. Although I didn't get an error message. The rules are if D14 is less than 45 do formula 1, if D14 is greater than 45 but less than 120 do formula 2 and if D14 is greater than 120 then do formula 3.

    So that means if D14 is equals let's say 68 it would come up with this result would be 71.

    Maybe I don't need the and statement???

    C


    Originally posted by DavidCoutts
    Hi

    This Formula should work for you.

    =IF(D14<45,((D14-30)*75+45),IF(AND(D14>45,D14<120),((D14-60)*75) + 65,((D14-120)*75 +125)))


    David
    CAT :-D

  4. #4
    Join Date
    Feb 2004
    Posts
    3
    IF(D14<45,((D14-30)*75+45),IF(AND(D14>45,D14<120),((D14-60)*75)+65),(D14-120)*75 +125)))

  5. #5
    Join Date
    Feb 2004
    Location
    Vermont
    Posts
    53
    Originally posted by goodthing44
    IF(D14<45,((D14-30)*75+45),IF(AND(D14>45,D14<120),((D14-60)*75)+65),(D14-120)*75 +125)))

    So if cell D14 = 68 why do I get a value of 665 with this formula. I should get a value of 71.
    CAT :-D

  6. #6
    Join Date
    Oct 2003
    Posts
    1,091
    Your condition is if it more than 45 and less than 120 then the formula is:

    (D14-60)*75 + 65

    With 68 as the entry in D14 you get:

    (68-60)*75 + 65 = 8*75 + 65 = 600 + 65 = 665
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  7. #7
    Join Date
    Oct 2003
    Posts
    1,091
    I did a little experimenting. Is "75" in your formulas signifying percent? I suspect that it is. When I changed the number to 0.75 and used that as the multiplier, then with 68 in D14 the result would be 71.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  8. #8
    Join Date
    Feb 2004
    Location
    Vermont
    Posts
    53
    Originally posted by shades
    I did a little experimenting. Is "75" in your formulas signifying percent? I suspect that it is. When I changed the number to 0.75 and used that as the multiplier, then with 68 in D14 the result would be 71.
    No what is occuring is this.

    If a client had let's say 68 minutes for the month price is then determine the following way.

    if less than 60 mins they are billed for anything over 30 mins at $.75 per min with a min charge of $45. Anything over 60 but less than 120 mins they are billed $.75/min plus a min. charge of $65 and if the total min for the month are over 120 mins they are billed $.75/min plus $125

    So this would mean the client that had a total of 68 mintues would pay.

    ((68-30)*.75)+45=$73.50 that the customer would pay for this month.

    CAT
    CAT :-D

  9. #9
    Join Date
    Oct 2003
    Posts
    1,091
    Guess, I'm confused now.

    Originally posted by carley465
    So if cell D14 = 68 why do I get a value of 665 with this formula. I should get a value of 71.
    I thought this was what you wanted for a result. But now with this post, it doesn't seemt hat you want that.

    Originally posted by carley465
    No what is occuring is this.

    If a client had let's say 68 minutes for the month price is then determine the following way.

    if less than 60 mins they are billed for anything over 30 mins at $.75 per min with a min charge of $45. Anything over 60 but less than 120 mins they are billed $.75/min plus a min. charge of $65 and if the total min for the month are over 120 mins they are billed $.75/min plus $125

    So this would mean the client that had a total of 68 mintues would pay.

    ((68-30)*.75)+45=$73.50 that the customer would pay for this month.

    CAT
    Now I also have to ask where the 45 came from in the original post, because now it seems as if you are using 60 as the criteria?

    Originally posted by carley465
    =If(D14<45), ((D14-30)*75 + 45), If(AND(D14>45)(D14<120), ((D14-60)*75) + 65, If(D14>120),((D14-120)*75)+125
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •