Help with setting a min AND max value within a single cell

[Braslava]

Hello,

I am having trouble setting a minimum value and a maximum vaule for a single cell.

For example, I am trying to have a min and max value for the equation A1*B1/C1, where A1 is a user manipulable cell and I want the value displayed restricted to between the set limits.

Thanks for your help.

[Fotis1991]

Hi and welcome to the forum

For me, your request, is not clear. Can you give us a better example, or even better to upload a small sample workbook for us?

Maybe Custom Validation will be the solution...

[Braslava]

Ok, I have included the line I am trying to work on.

What I am trying to achieve is the weight can be entered and the Dose(mg) and the Amount(mL) will be generated. The Dose(mg) is done simply. The Amount(mL) column I want to have it limited to minimum 0.1 and maximum 0.5.

More Indepth. The Weight is multplied by the Recommended dose which gives us the Dose(mg). The Dose(mg) is then divided by the Available Concentration which gives the Amount(mL). There is a minimum amount and a maximum amount of the drug that can be given. I want to set the min and the max so that when the wieght is entered it will not return a value less than the min or more than the max.

Hope that clairfies things a bit more. Sorry for the confusion.

Cheers

[npamcpp]

Please get rid of all the merged cells. It makes it nearly impossible to identify a specific cell.

Also, you do not need a separate column to specify the unit of measure. You can use a custom format to do that. Saves heaps of work.

I've cleaned up the sheet and assigned range names to make formulas more obvious. I've added two columns for the min and the max values.

Finally, I've added a formula that returns an error message if the calculated value of Dose divided by Available concentration is not between the min and the max values.

=IF(OR(Dose/AvConcentration<min,Dose/AvConcentration>max),"not valid",Dose/AvConcentration)

Have a look at the attached file.

This is a starting point. We can work on this to fine tune it to your needs.

[Braslava]

Thanks.

I need it to return an actual value. If the value is below the min (0.1) then I need it to return 0.1 for the value. Same if the value is above 0.5 and if the value is between 0.1 and 0.5 I need it to give the actual value (Say 0.23, for example)

Thanks for your time.

[Cutter]

Try

=MEDIAN(A1*B1/C1,0.1,0.5)

[npamcpp]

Use this formula in cell E5 of the workbook I attached above

=IF(Dose/AvConcentration<min,min,IF(Dose/AvConcentration>max,max,Dose/AvConcentration))

[Braslava]

Thanks for all the help. npamccp's solution worked perfectly.