Conditional Multiplication question

[camo704773]

Hi,

Can anyone help me to turn the below into a working function for a single cell? I tried using this but the arguments conflict -> =IF(AND(C3>=176,C3<=500),C3*2.5,)+IF(AND(C3>=501,C3<=1000),C3*2,)


$2.5 per mile on miles (176-500) + $2 per mile (501-1000)


Thanks

[Fluff13]

How about

=IF(AND(C3>=176,C3<=500),C3*2.5,IF(AND(C3>=501,C3<=1000),C3*2,))

[AliGW]

Welcome to the forum.

Is this a tiered rate or flat rate calculation?

If the latter:

=C3*LOOKUP(C3,{0,176,501},{0,2.5,2})

[Pete_UK]

Surely the second IF should be:

... (C3-500)*2 ...

The first IF will not give you the enhanced rate for the first 500 miles if you exceed that. Please explain the constraints more clearly.

Do you not get anything if you have done less than 176 miles?

Hope this helps.

Pete

[camo704773]

Anything under 176 generates a charge of $1000. I had no trouble generating that calculation, so I did not include it in the question. Apologies for the lack of clarity.

[Pete_UK]

Okay, the full set of criteria can be obtained with this:

=IF(C3<176,1000,IF(C3<=500),C3*2.5,IF(C3<=1000,(C3-500)*2+500*2.5,"Above 1000 miles")))

You haven't given the criteria for larger than 1000 miles (perhaps that won't occur ?)

Hope this helps.

Pete

[camo704773]

Okay, the full set of criteria can be obtained with this:

=IF(C3<176,1000,IF(C3<=500),C3*2.5,IF(C3<=1000,(C3-500)*2+500*2.5,"Above 1000 miles")))

You haven't given the criteria for larger than 1000 miles (perhaps that won't occur ?)

Hope this helps.

Pete

I see now. Thank you for your help Pete. This will work.

[camo704773]

Surely the second IF should be:

... (C3-500)*2 ...

The first IF will not give you the enhanced rate for the first 500 miles if you exceed that. Please explain the constraints more clearly.

Do you not get anything if you have done less than 176 miles?

Hope this helps.

Pete

In totality, this is what I came up with =IF(C3<176,1000,IF(AND(C3>=176,C3<=500),C3*2.5,)+IF(AND(C3>=501,C3<=1000),C3*2,))

If C3= 1000, I am trying to find a way to make the rate come out to $2,250=500*2.5+500*2.

[camo704773]

Hi Fluff & Ali,

Thank you for your help. For example, if C3=1000 miles, I would want the rate to come out to $2,250=500*2.5+500*2.

[AliGW]

Hi Fluff & Ali,

Thank you for your help. For example, if C3=1000 miles, I would want the rate to come out to $2,250=500*2.5+500*2.

That's a TIERED calculation.

[camo704773]

That's a TIERED calculation.

Yes, sorry for not specifying.

[AliGW]

Well, I asked the question in post #2!!!

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.

[camo704773]

Thank you Ali!