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

[neuroscientia]

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!

 

[jtakw]

Hi,

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

 

[neuroscientia]

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 !

 

[jtakw]

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.

 

[neuroscientia]

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?

 

[jtakw]

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
	A	B	C	D	E
1	Solution needed/ml	tube size/ml	Solution/tube	Quatity	Total yield/ml
2	146	50	49	3	147
3	234		47	5	235
4	821		49	17	833
Sheet1059

Cell Formulas
Range	Formula
C2:C4	C2	=ROUNDUP(A2/D2,0)
D2:D4	D2	=MATCH(TRUE,A2/ROW($1:$100)<=B$2,0)
E2:E4	E2	=C2*D2
Press CTRL+SHIFT+ENTER to enter array formulas.