Excel Formula help, nested > and < then

[Ben171]

HI, please see below table as example, say they are columns A B C D

You can see we have a standard carriage charge, but i am wanting to test whether we can charge a flat rate of 1.5% (of product value) as a new carriage cost. However we do not want to charge any carriage that is less than 7.50, or any carriage that is over £100

For example:
order 1, has a carriage cost of 1.80, so should be set to 7.50,
order 2, has a carriage cost of 155, so should be 100
order 3 is inbetween 7.50 and 100, so can stay at 40.80

Order Number	Carriage charge	1.5% of product val	minimum 7.50 / max 100
1​	£15.00​	£1.80​	£7.50​
2​	£35.00​	£155.26​	£155.26​
3​	£15.00​	£40.80​	£40.80​

 

[jasonb75]

If you just want to return the correct value then it's a basic median.

=MEDIAN(7.50,100,1.5% of product val)

 

[Toadstool]

Hi Ben171,

Is this what you wanted?

Ben171.xlsx
	A	B	C	D
1	Order Number	Carriage charge	1.5% of product val	minimum 7.50 / max 100
2	1	£15.00	£1.80	£7.50
3	2	£35.00	£155.26	£100.00
4	3	£15.00	£40.80	£40.80
Sheet1

Cell Formulas
Range	Formula
D2:D4	D2	=IF(C2<7.5,7.5,IF(C2>100,100,C2))

 

[Ben171]

Hi Ben171,

Is this what you wanted?

Ben171.xlsx
	A	B	C	D
1	Order Number	Carriage charge	1.5% of product val	minimum 7.50 / max 100
2	1	£15.00	£1.80	£7.50
3	2	£35.00	£155.26	£100.00
4	3	£15.00	£40.80	£40.80
Sheet1

Cell Formulas
Range	Formula
D2:D4	D2	=IF(C2<7.5,7.5,IF(C2>100,100,C2))

This is exactly what i was after, thank you!

I was getting myself confused but it really is that simple

Thanks

 

[jasonb75]

I was getting myself confused but it really is that simple

Actually it's even simpler than that, as I pointed out in post 2.