Formula to Identify value less than another, then make original value less (multipliable to get original value)

neuroscientia

New Member
Joined
Mar 22, 2022
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
Hi, sorry of this is confusing.

Let me use an example to clarify.

I am making up a solution, e.g., 146ml. However I only own 50ml tubes, therefore the calculations used to make up the original would need to be altered so I could use multiple of my tubes. In this case I would make 4 x 37ml tubes of the solution (with corresponding concentrations), which would give me 148ml, which is more than I need which is OK (do not want less than I need).

Is there a way to do this as a formula on excel?

I have tried SUMIF, but the sum-range doesn't get me the information I need.

Thanks!
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Hi,

Why not 3 x 49ml tubes, giving you 147ml total ???
 
Upvote 0
Hi,

Why not 3 x 49ml tubes, giving you 147ml total ???
Hiya.

I will be making up a few different solutions which require various calculations (I will be adding different compounds which themselves need their dilutions calculated).

So when I get my final volumes I would like to be able to automatically determine how to split my volume so that it can fit into multiple 50ml tubes (rather than doing this manually).

Hope this makes sense !
 
Upvote 0
No, it doesn't.

My question is, why 4 x 37ml, and why Not 3 x 49ml ???
What logic are you going by.

If it's supposed to be Closest to your 146ml, 3 x 49ml would produce 147ml, whereas 4 x 37ml would be 148ml, so logically, it should be 3 x 49ml.
Otherwise, it could be almost any combo:

15 x 10ml
4 x 50ml
5 x 30ml
etc., etc.
 
Upvote 0
Well ideally I would use the smallest amount of tubes to get the final volume required, to avoid wasting materials.

I'm guessing this is too complex for excel and will have to be done manually?
 
Upvote 0
My question is, why 4 x 37ml, and why Not 3 x 49ml ???
What logic are you going by.

If it's supposed to be Closest to your 146ml, 3 x 49ml would produce 147ml, whereas 4 x 37ml would be 148ml, so logically, it should be 3 x 49ml.
Otherwise, it could be almost any combo:

15 x 10ml
4 x 50ml
5 x 30ml
etc., etc.

Well ideally I would use the smallest amount of tubes to get the final volume required, to avoid wasting materials.

So, you agree with my comments in Post # 4 ?

Assuming you want Whole ml, meaning no fractions.
See if this works for you:

Book3.xlsx
ABCDE
1Solution needed/mltube size/mlSolution/tubeQuatityTotal yield/ml
214650493147
3234475235
48214917833
Sheet1059
Cell Formulas
RangeFormula
C2:C4C2=ROUNDUP(A2/D2,0)
D2:D4D2=MATCH(TRUE,A2/ROW($1:$100)<=B$2,0)
E2:E4E2=C2*D2
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,106
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top