Division result not a whole number

[julhs]

L13 is “Total to make” (a manual input, between 1 – 40 litres)
N11 is “Batch size” (selected from Combo, 0.5 – 4.00 with 0.5 increments)
N13 is result “# of Batches require”

Currently; formula in N13 is (L13/N11)/1000
L13 = 10000
N11 = 4.00
Therefore, N13 is (10000/4.00)/1000 = 2.500

However if;
N11 = 0.5, N13 would = 20.00
= 1.00 = 10.00
= 2.00 = 5.00
=2.50 = 4.00
=3.00 = 3.333
=3.50 = 2.857
=4.00 = 2.50

Want a way to handle things when L13/N11 does not divide out to a whole number.
Can’t use ROUND as that will affect the figures that subsequently use the value in N13.
Only thing I could think of, and NOT the most subtle way was a cell prompt like “Check Batch Size”, but I just can’t figure out the syntax for that.

But there must be a better way than that?

 

[GlennUK]

So an entry of 4 ... giving 2.5 ... is illegal because the result is not a whole number?

 

[julhs]

Yes illegal
But entry of 2.5 ....giving 4 is ok because 10000 will divide by 2.5 giving whole number result of 4.

 

[julhs]

For clarification 2.5 is multiplied by 1000 giving 2500 which will divide into 10000 by 4

 

[GlennUK]

Do some calculation so you can see what Batches result in whole numbers, and test against that for messages, or even use that list as the source for the combo for batch:

Book3 (version 1).xlsb
	P	Q	R	S	T
2	All Batch	results	OK?	Count	Short batch
3	0.5	25	TRUE	1	0.5
4	1	12.5	FALSE	1	2.5
5	1.5	8.3333333	FALSE	1
6	2	6.25	FALSE	1
7	2.5	5	TRUE	2
8	3	4.1666667	FALSE	2
9	3.5	3.5714286	FALSE	2
10	4	3.125	FALSE	2
Sheet3

Cell Formulas
Range	Formula
Q3:Q10	Q3	=$L$13/P3
R3:R10	R3	=MOD(Q3,1)=0
S3:S10	S3	=COUNTIF(R$2:R3,TRUE)
T3:T10	T3	=IF((ROW()-ROW($T$2))>MAX($S$3:$S$10),"",INDEX($P$3:$P$10,MATCH((ROW()-ROW($T$2)),$S$3:$S$10,0)))

 

[GlennUK]

... have a message like this:

 

[julhs]

Thanks Glenn,
Only looking at it briefly its an interesting solution, will have a proper look tomorrow .

Many thanks

 

[julhs]

Your suggestion definitely solves my question!

I was in the process of incorporating it into my existing sheet layout and realised there was going to be a mass of re-working required. It got me thinking about your use of MOD(Q3,1)=0 in R3:R10 and I have come up with a solution that requires very little change to my existing lay out.
Only required moving the N13 formula (L13/N11)/1000 e.g. (10000/4.00)/1000 = 2.500 to a “Hidden” cell G11, then use IF(MOD(G11,1)=0,(L13/N11)/1000,"Change batch size") in N13.
It does mean I don’t get the prompt to the batch sizes that do calculate out that yours gives; just get prompt of "Change batch size". But ultimately there IS Only 8 “Batch Sizes” to choose from.

It’s a work-around I’ve used in the past due to lack of knowledge to construct anything like yours!!

Thank you Glenn for all your help.

PS. I see the 2 ComboBoxes haven't shown on the "Mini Sheet", I assume because they are ActiveX controls

00 Fert-Chemical-Nozzle calculator Master.xlsm
	B	C	D	E	F	G	H	I
3
4		Notes: 1.	Manual Entry of desired values in yellow cells
5		. 2.	Select Mixing Ratio from Combox Drop Down
6		. 3.	Select Batch Mix Size from Combox Drop Down
7
8	To Calculate The Mixing Ratio required to make a Ferrous Sulphate concentrate mix myself from Ferrous Sulphate powder
9
10		Enter quantity of IRON in gms you want to apply /m2 of grass	Select Mixing Ratio	Quantity of concentrate you want to make		Select batch mix size (ltr)	Total Concentrate to make
11						2.50	< Batch calculation. Will be hidden
12						4.00	10.0 ltr
13				IN TOTAL in mls		Batches required	Total Iron Required
14		4	12500 ml	10000		Change batch size
15	So Add	0.160 gms	of Iron and	MADE UP TO	1 ml	Iron per batch
16	Or	160.0 gms	of Iron and	MADE UP TO	1000 mls	Change batch size
17
Fert Calc Master (3)

Cell Formulas
Range	Formula
G11	G11	=(E14/G12)/1000
H12	H12	=E14/1000
G14	G14	=IF(MOD(G11,1)=0,(E14/G12)/1000,"Change batch size")
H15	H15	=IF(G14="Change batch size","",IF(ISTEXT(C14),"Check gms",IF(ISTEXT(D14),"Check ratio",IF(ISTEXT(E14),"Check quantity",IF(C14="","Enter gms",IF(D14="","Enter ratio",IF(E14="","Enter quantity",(G14*G16)/1000)))))))
C15	C15	=IF(ISTEXT(C14),"Check gms",IF(ISTEXT(D14),"Check ratio",IF(ISTEXT(E14),"Check quantity",IF(C14="","Enter gms",IF(D14="","Enter ratio",IF(E14="","Enter quantity",(C14*500)/D14))))))
C16	C16	=IF(ISTEXT(C14),"Check gms",IF(ISTEXT(D14),"Check ratio",IF(ISTEXT(E14),"Check quantity",IF(C14="","Enter gms",IF(D14="","Enter ratio",IF(E14="","Enter quantity",(C14*500/D14)*1000))))))
G16	G16	=IF(G14="Change batch size","Change batch size",IF(G14>=1,(E14/F16)*(C16/G14),(C16/(G12*G14))))