1. Registered User
Join Date
Feb 2004
Location
Vermont
Posts
53

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

2. Registered User
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. Registered User
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

4. Registered User
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. Registered User
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.

6. Registered User
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

7. Registered User
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.

8. Registered User
Join Date
Feb 2004
Location
Vermont
Posts
53
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

9. Registered User
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

Posting Permissions

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