Excel Cell Min and Max Limits

Hi, was wondering if someone could give me some guidance. I work in Insurance and have a spreadsheet which requires [oil] values to be entered into Column J. I then have three additional columns (K,L & M) and I require them to do the following:

K: take the value from J but cap it at $100,000,000.
L: take the value from J but have a minimum value of $100,000,000 and a maximum value of $400,000,000.
M: take the value from J but have a minimum value of $500,000,000 and a maximum value of $100,000,000.

For example, if i were to enter a value of $650,000,000 into Column J, Column K should show $100,000,000, Column L should show $400,000,000 and Column M should show $50,000,000. Likewise if I were enter $15,000,000 into Column J, Column K would show $15,000,000, Column L would show $- and Column M would show $-.

Any help would be greatly appreciated. Thank you.

Try

=MIN(J3,100000000)

=IF(J3<100000000,0,MIN(J3,400000000))

and

=IF(J3<100000000,0,MIN(J3,500000000))

in K3:M3, respectively and copy down.

Hello Graham,

in L3 try

=MEDIAN(0,400000000,J3-K3)

and in M3

=J3-K3-L3

Edit: Although I'm not really clear from your examples, if column M needs to have a maximum amount of 100,000,000 then change M3 to

=MIN(100000000,J3-K3-L3)

Try:

=IF(J3<100000000,0,IF(J3>500000000,400000000,J3-100000000))

and

=IF(J3<100000000,0,MIN(100000000,J3-K3-L3))

respectively, and copied down.

You are welcome,

Can you please remember to mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

Try:

=MIN(N(J3),100000000)