select from a range if maximum exceeded

**DoctorMac** · 09-23-2008, 12:05 PM

I hope the title makes sense. I have a spreadsheet that I'm trying to use to calculate volumes of liquid. For each liquid there is a minimum and maximum amount that can be included in a vial, and a checkbox to indicate if it should be included or not. What I'm having trouble with is some logic (other than a million if= statements) to determine the amount of each needed.

If only a few are selected, using the max value for each is fine as it is below the volume of the vial, so I want to use the max. But if enough are selected, the max for each would exceed the volume of the vial, and I want to pick a percentage of each component's range until we get a volume that will fit in the vial (ie, it figures out that 84% of the max for each will fit, but knows never to drop below the min).

If possible I would greatly prefer to not use VBA to do this.

Thanks for your thoughts,
-Rob

**NBVC** · 09-23-2008, 12:28 PM

It might help if you posted a sample spreadsheet showing what you are working with.... and what results you might expect.

**DoctorMac** · 09-23-2008, 12:50 PM

Here is a basic example. The real sheet is much larger, and the min and max volumes are calculated and not manually entered, but that shouldn't matter. The vial # can be 1-4, but I think if it can be done for one vial I can figure out how to apply it to all.

In this example all the substances set to be included in vial 1 would be more than the 5mL limit of the vial if I set each to the max. I need it to tell me to use a volume that is about 88% of the max (to the nearest 0.1) to get under 5mL. But if I set the vial size to 1mL it should tell me that it cannot be done.

The only thing that has sprung to mind is to calculate the percent needed by B10/SUMIF(D3:D8,1,C3:C8) and then use that # x the max value in column C. But that does not address the lower limit issue.

Thanks,
-Rob

**NBVC** · 09-23-2008, 01:09 PM

Does this formula in E3, copied down do it?

Please Login or Register  to view this content.

**DoctorMac** · 09-23-2008, 01:28 PM

Not exactly, that returned a value of 2 for E8 and it should be zero as it is not in a vial. Amazing how just figuring out what your question really is helps you solve it. I think I'm getting close. Here is what I have in E3 right now, but I still need some way to keep it from dropping below the min (and telling me that it's not possible).

Please Login or Register  to view this content.

Maybe I could nest another IF statement to say if the result of that formula is < row B to return an error message?

The other problem is that if you really increase the max, say change C4 to =8, it drives down the other substances, instead of hitting their lower limit and just using a lesser percentage of substance B.

EDIT: I see now that your suggestion has a way to account for the total being >B10, which mine lacks. But the problem of a very high max still applies.

-Rob

**NBVC** · 09-23-2008, 01:39 PM

If you are only interested in Vial #1, then...

Please Login or Register  to view this content.

copied down.

If you enter 1 in B10, you will get the error message...

**DoctorMac** · 09-23-2008, 01:59 PM

That gives me 1.9 in E4 and E5, it should be 1.7.

-Rob

**NBVC** · 09-23-2008, 02:01 PM

Are you sure? I get 1.7 in both those cells.

See attached.

**DoctorMac** · 09-23-2008, 02:40 PM

Weird. I did, but I've fiddled with so many things in that example that it's very possible that I messed something else up.

I decided that before I made everything pretty, I should work it out "longhand" if you will. As such this has more cells than needed, but lets me see what I'm trying to do. It almost works, but if you change the vial size to 4 it puts too much in. It avoids dropping below the minimums though. I just need to figure out some logic to keep it from exceeding the total, and then to comrpess things in to fewer cells.

-Rob

**NBVC** · 09-23-2008, 02:59 PM

Probably, it is best you keep it the way you have it...and just hide the "helper" columns....

This way, you are not repeating the same functions in the formula...

**DoctorMac** · 09-23-2008, 03:02 PM

Yeah, for starters hiding columns is surely better.

The problem is that I still can't figure out what to do to knock down the high values that make things exceed the vial volume. Everything I think of leads to circular logic patterns.

-Rob

**NBVC** · 09-23-2008, 03:06 PM

Would it be as easy as using the MIN() function?

E.g. in E3: =MIN(C3,IF(D3=0,0,IF(G3>H3,G3,H3))) copied down

**DoctorMac** · 09-23-2008, 03:12 PM

That is a slightly more elegant way of selecting the low value. But it still leads to allowing for too much in the vial. Somehow the individual values in E need to look at the total in E10 and if it's > B10 decrease themselves if possible. If it's not possible, then we need to return an error message.

-Rob

**NBVC** · 09-23-2008, 03:28 PM

How about if you move the formula you currently have in column E to a new "helper" column, say column I... and sum those at the bottom...

Then in E3, use =IF($I$10>$B$10,"Below Min",I3) copied down... that would eliminate circular referencing