Formula

[Joey172]

I am trying to set up a formula for a column of cells but I don't know how
Here are the criteria
If the rate is 0.003515 and weight multiplied by rate < 11.50, then the charge is 11.50
If false, the rate is weight multiplied by rate

I can get that part but there are more criteria
If rate IS NOT 0.003515 and weight multiplied by rate < 15.00, then teh charge is 15.00
If false, the rate is weight multiplied by rate

I hope this isn't too confusing...Can anyone tell me how to set up such a formula? Thanks.

Excel User

 

[Brian from Maui]

I am trying to set up a formula for a column of cells but I don't know how
Here are the criteria
If the rate is 0.003515 and weight multiplied by rate < 11.50, then the charge is 11.50
If false, the rate is weight multiplied by rate

I can get that part but there are more criteria
If rate IS NOT 0.003515 and weight multiplied by rate < 15.00, then teh charge is 15.00
If false, the rate is weight multiplied by rate

I hope this isn't too confusing...Can anyone tell me how to set up such a formula? Thanks.

Excel User

How many more criterias?

 

[Legacy 21301]

=IF(AND(Rate=0.003515,Rate*Weight<11.5),11.5,IF(AND(Rate<>0.003515,Rate*Weight<15),15,Rate*Weight))

 

[Joey172]

Hey thanks...that <> was the trick.....I didn't know how to put an unequal sign

 

[tactps]

Assume cell A1 contains the rate, and column B contains the weight.

In cell C1:
=IF(AND($A$1=0.003515,$A$1*$B1<11.5),11.5,IF(AND($A$1<>0.003515,$A$1*$B1<15),15,$A$1*$B1))

Then copy down.

 

[berg891832]

Try this although untested. May get you on the right track nonetheless

if(and(rate=0.003515,weight*rate<11.50),11.50,if(and(rate<>0.003515,weight*rate<15),15,weight*rate))

 

[Joey172]

how about that same formula, but adding a roundup function?? thanks. nesting is so weird

 

[berg891832]

=ROUNDUP(IF(AND(A1=0.003515,B1*A1<11.5),11.5,IF(AND(A1<>0.003515,B1*A1<15),15,B1*A1)),2)

berg