Limiting formula to set value

[Jman1992]

Hi I'm trying to create a formula which limits its self at a certain value, the formula I am using up to now works fine however I need it to limit its self to a maximum value of 845.

=IF(B14>MIN(200)*OR(MAX(1500)),((B14-200)/100)*65,0)

any suggestions on how to do this?

thanks

 

[dreid1011]

Welcome to the board.

The TRUE result needs to be limited to 845?

Perhaps something like this:

=IF(B14>MIN(200)*OR(MAX(1500)),MAX(((B14-200)/100)*65,845),0)

 

[Joe4]

Welcome to the Board!

I think we are going to need you to explain exactly what the formula should do in detail, because you are not using MIN or MAX correctly.

There is no point in applying the MIN or MAX functions to a single static number. You use them when being compared to multiple numbers.
MIN(200) is just the same as 200 since you are not comparing it to any other number here, so why use MIN at all here?
Same thing with MAX(1500). The MAX function serves no purpose here.
The other values you want to compare the numbers to have to be within the parentheses ().

See here for more details: http://www.contextures.com/excelminmaxfunction.html

 

[Eric W]

Welcome to the board.

I doubt that formula does what you want, there's some odd things about the way you coded it. Maybe:

=IF(AND(B14>200,B14<=1500),(B14-200)/100*65,0)

or

=MEDIAN(0,(B14-200)/100*65,865)

 

[Jman1992]

Hi all

thanks for such quick responses! basically I have a pricing spreadsheet. In Cell B14 is an anticipated amount of usage and there is a reducing scale of costs. The cost between 201-1500 is £65 per 100 items. I have limited B14 to only allow entries of 100's.

Once the value goes above the 1500 mark there is another line which is populated. As its only every 100 between 200 and 1500 that will need a value inputing I wanted to max the output of this formula to £845 (13 x £65)

My formula does work in but wont limit. @Joe4 I have never used the min/max ones before but will try your suggestion and see if I can remove this bits

many thanks again