Formula

Joey172

New Member
Joined
Feb 29, 2004
Messages
3
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
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Brian from Maui

MrExcel MVP
Joined
Feb 16, 2002
Messages
8,458
Joey172 said:
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?
 
L

Legacy 21301

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

Joey172

New Member
Joined
Feb 29, 2004
Messages
3
Hey thanks...that <> was the trick.....I didn't know how to put an unequal sign
 

tactps

Well-known Member
Joined
Jan 20, 2004
Messages
3,459

ADVERTISEMENT

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

Well-known Member
Joined
May 2, 2002
Messages
509
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

New Member
Joined
Feb 29, 2004
Messages
3
how about that same formula, but adding a roundup function?? thanks. nesting is so weird
 

berg891832

Well-known Member
Joined
May 2, 2002
Messages
509
=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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,914
Messages
5,598,840
Members
414,261
Latest member
KatieBsc

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top