Formula & Condition Formatting

J

julhs

Active Member
Joined
Dec 3, 2018
Messages
476
Office Version
  1. 2010
Platform
  1. Windows
This is a chemical application calculator that I’m in process of trying to constructing.
Looking for help with formula in E17:E19 & F17:F19 and Condition formatting for same.

Ultimate goal is for these cells to display as “0.000 lts” or “000 mls”.

Can see there are several parts to this. Initial calculation needs to establish if resulting value is above or below “0.00”. If above (eg 1.00) simply displayed as 1.00, but if it’s below (eg 0.10) it then needs to convert "lts" to "mls", so is displayed as 100.
Then some-how with condition formatting set the suffix accordingly.
So:-
1.00 is displayed as 1.00 lts
but 0.100 is converted and displayed as 100 mls

This is a work in progress so there is some formula and condition formatting that still contain aspects of testing

A test used in E17
VBA Code: 
=IF(ISTEXT(E10),"",IF(ISTEXT(E11),"Check entres",IF(ISTEXT(E12),"Check entres",IF(ISTEXT(E13),"Check entres",IF(E13-TRUNC(E13)=0,"No PART Tank",IF((E13-TRUNC(E13))*(E4*1000)/1000<1,"xx",((E13-TRUNC(E13))*(E4*1000)/1000)))))))

Book1.xlsm
ABCDEFGHIJKL
1
2
3
4Tank size in ltrs25E4=Input Tank size in ltrs (25 or 15)
5ml / meterltr/ha
6Product Dose rate1.0 ml / m2E6:E9=Data Input Area
7# of Passes1
8Total Volume Rate / m250 ml / m2For simple testing:-
9Size of Area to treat19 (m2)to affect E17:E19, change value in E9 to
10Product Dose rate / Pass1.0 ml / m2  anything (eg 20-1100)
11Water Dose rate / Pass49.00 ml / m2 
12Total Water rate49.00 ml / m2 
13Tanks required0.0380 
14
15Product per FULL tankNo Full tank 
16Water per FULL tankNo Full tank 
17PART tank TOTAL.950 ltr 
18Product FOR PART tank19 ml 
19Water FOR PART tank.931 ltr 
20
21Product usedFerromexUltimatly put reset button here
22Product container size20 ltr
23Product Cost£ 92.25
24Unit Cost of Product0.461 p / ml 
25Total Quantity Product used19 ml 
26Total Cost of Product used£ 0.09 
27
28
29
Sheet6
Cell Formulas
RangeFormula
E10E10=IF(AND(E6>0,F6>0),"Enter ONLY mls or lts",IF(AND(E6>0,F7>0),"Enter ONLY mls or lts",IF(AND(E6>0,F8>0),"Enter ONLY mls or lts",IF(AND(E6>0,F9>0),"Enter ONLY mls or lts",IF(AND(E7>0,F6>0),"Enter ONLY mls or lts",IF(AND(E7>0,F7>0),"Enter ONLY mls or lts",IF(AND(E7>0,F8>0),"Enter ONLY mls or lts",IF(AND(E7>0,F9>0),"Enter ONLY mls or lts",IF(AND(E8>0,F6>0),"Enter ONLY mls or lts",IF(AND(E8>0,F7>0),"Enter ONLY mls or lts",IF(AND(E8>0,F8>0),"Enter ONLY mls or lts",IF(AND(E8>0,F9>0),"Enter ONLY mls or lts",IF(AND(E9>0,F6>0),"Enter ONLY mls or lts",IF(AND(E9>0,F7>0),"Enter ONLY mls or lts",IF(AND(E9>0,F8>0),"Enter ONLY mls or lts",IF(AND(E9>0,F9>0),"Enter ONLY mls or lts",IF(F6<>"","",IF(F7<>"","",IF(F8<>"","",IF(F9<>"","",IF(E6="","Enter dose rate ",IF(E6=0,"Check dose rate",IF(ISTEXT(E6),"Check dose rate",IF(E7="","Enter # of Passes",IF(E7=0,"Check # of Passes",IF(ISTEXT(E7),"Check # of Passes",IF(E8="","Enter water rate",IF(E8=0,"Check water rate",IF(ISTEXT(E8),"Check water rate",IF(E9="","Enter area to treat",IF(E9=0,"Check area to treat",IF(ISTEXT(E9),"Check area to treat",IF((E6/E7)=E8,"kk",(E6/E7))))))))))))))))))))))))))))))))))
F10F10=IF(AND(E6>0,F6>0),"Enter ONLY mls or lts",IF(AND(E6>0,F7>0),"Enter ONLY mls or lts",IF(AND(E6>0,F8>0),"Enter ONLY mls or lts",IF(AND(E6>0,F9>0),"Enter ONLY mls or lts",IF(AND(E7>0,F6>0),"Enter ONLY mls or lts",IF(AND(E7>0,F7>0),"Enter ONLY mls or lts",IF(AND(E7>0,F8>0),"Enter ONLY mls or lts",IF(AND(E7>0,F9>0),"Enter ONLY mls or lts",IF(AND(E8>0,F6>0),"Enter ONLY mls or lts",IF(AND(E8>0,F7>0),"Enter ONLY mls or lts",IF(AND(E8>0,F8>0),"Enter ONLY mls or lts",IF(AND(E8>0,F9>0),"Enter ONLY mls or lts",IF(AND(E9>0,F6>0),"Enter ONLY mls or lts",IF(AND(E9>0,F7>0),"Enter ONLY mls or lts",IF(AND(E9>0,F8>0),"Enter ONLY mls or lts",IF(AND(E9>0,F9>0),"Enter ONLY mls or lts",IF(E6<>"","",IF(E7<>"","",IF(E8<>"","",IF(E9<>"","",IF(F6="","Enter dose rate",IF(F6=0,"Check dose rate",IF(ISTEXT(F6),"Check dose rate",IF(F7="","Enter # of Passes",IF(F7=0,"Check # of Passes",IF(ISTEXT(F7),"Check # of Passes",IF(F8="","Enter water rate",IF(F8=0,"Check water rate",IF(ISTEXT(F8),"Check water rate",IF(F9="","Enter area to treat",IF(F9=0,"Check area to treat",IF(ISTEXT(F9),"Check area to treat",IF(F6/F7=F8,"kk",(((F6*1000)/F7)/1000)/10)))))))))))))))))))))))))))))))))
E11E11=IF(AND(E6>0,F6>0),"Enter ONLY mls or lts",IF(AND(E6>0,F7>0),"Enter ONLY mls or lts",IF(AND(E6>0,F8>0),"Enter ONLY mls or lts",IF(AND(E6>0,F9>0),"Enter ONLY mls or lts",IF(AND(E7>0,F6>0),"Enter ONLY mls or lts",IF(AND(E7>0,F7>0),"Enter ONLY mls or lts",IF(AND(E7>0,F8>0),"Enter ONLY mls or lts",IF(AND(E7>0,F9>0),"Enter ONLY mls or lts",IF(AND(E8>0,F6>0),"Enter ONLY mls or lts",IF(AND(E8>0,F7>0),"Enter ONLY mls or lts",IF(AND(E8>0,F8>0),"Enter ONLY mls or lts",IF(AND(E8>0,F9>0),"Enter ONLY mls or lts",IF(AND(E9>0,F6>0),"Enter ONLY mls or lts",IF(AND(E9>0,F7>0),"Enter ONLY mls or lts",IF(AND(E9>0,F8>0),"Enter ONLY mls or lts",IF(AND(E9>0,F9>0),"Enter ONLY mls or lts",IF(F6<>"","",IF(F7<>"","",IF(F8<>"","",IF(F9<>"","",IF(E6="","Enter dose rate",IF(E6=0,"Check dose rate",IF(ISTEXT(E6),"Check dose rate",IF(E7="","Enter # of Passes",IF(E7=0,"Check # of Passes",IF(ISTEXT(E7),"Check # of Passes",IF(E8="","Enter water rate",IF(E8=0,"Check water rate",IF(ISTEXT(E8),"Check water rate",IF(E9="","Enter area to treat",IF(E9=0,"Check area to treat",IF(ISTEXT(E9),"Check area to treat",IF(E8-E6/E7<=0,"Check entres",(E8-E6)/E7)))))))))))))))))))))))))))))))))
F11F11=IF(AND(E6>0,F6>0),"Enter ONLY mls or lts",IF(AND(E6>0,F7>0),"Enter ONLY mls or lts",IF(AND(E6>0,F8>0),"Enter ONLY mls or lts",IF(AND(E6>0,F9>0),"Enter ONLY mls or lts",IF(AND(E7>0,F6>0),"Enter ONLY mls or lts",IF(AND(E7>0,F7>0),"Enter ONLY mls or lts",IF(AND(E7>0,F8>0),"Enter ONLY mls or lts",IF(AND(E7>0,F9>0),"Enter ONLY mls or lts",IF(AND(E8>0,F6>0),"Enter ONLY mls or lts",IF(AND(E8>0,F7>0),"Enter ONLY mls or lts",IF(AND(E8>0,F8>0),"Enter ONLY mls or lts",IF(AND(E8>0,F9>0),"Enter ONLY mls or lts",IF(AND(E9>0,F6>0),"Enter ONLY mls or lts",IF(AND(E9>0,F7>0),"Enter ONLY mls or lts",IF(AND(E9>0,F8>0),"Enter ONLY mls or lts",IF(AND(E9>0,F9>0),"Enter ONLY mls or lts",IF(E6<>"","",IF(E7<>"","",IF(E8<>"","",IF(E9<>"","",IF(F6="","Enter dose rate",IF(F6=0,"Check dose rate",IF(ISTEXT(F6),"Check dose rate",IF(F7="","Enter # of Passes",IF(F7=0,"Check # of Passes",IF(ISTEXT(F7),"Check # of Passes",IF(F8="","Enter water rate",IF(F8=0,"Check water rate",IF(ISTEXT(F8),"Check water rate",IF(F9="","Enter area to treat",IF(F9=0,"Check area to treat",IF(ISTEXT(F9),"Check area to treat",IF(((F8*1000)/10000)-((F6*1000)/10000)/F7<=0,"Check entres",(((F8*1000)/10000)-((F6*1000)/10000)/F7))))))))))))))))))))))))))))))))))
E12E12=IF(AND(E6>0,F6>0),"Enter ONLY mls or lts",IF(AND(E6>0,F7>0),"Enter ONLY mls or lts",IF(AND(E6>0,F8>0),"Enter ONLY mls or lts",IF(AND(E6>0,F9>0),"Enter ONLY mls or lts",IF(AND(E7>0,F6>0),"Enter ONLY mls or lts",IF(AND(E7>0,F7>0),"Enter ONLY mls or lts",IF(AND(E7>0,F8>0),"Enter ONLY mls or lts",IF(AND(E7>0,F9>0),"Enter ONLY mls or lts",IF(AND(E8>0,F6>0),"Enter ONLY mls or lts",IF(AND(E8>0,F7>0),"Enter ONLY mls or lts",IF(AND(E8>0,F8>0),"Enter ONLY mls or lts",IF(AND(E8>0,F9>0),"Enter ONLY mls or lts",IF(AND(E9>0,F6>0),"Enter ONLY mls or lts",IF(AND(E9>0,F7>0),"Enter ONLY mls or lts",IF(AND(E9>0,F8>0),"Enter ONLY mls or lts",IF(AND(E9>0,F9>0),"Enter ONLY mls or lts",IF(F6<>"","",IF(F7<>"","",IF(F8<>"","",IF(F9<>"","",IF(E6="","Enter dose rate",IF(E6=0,"Check dose rate",IF(ISTEXT(E6),"Check dose rate",IF(E7="","Enter # of Passes",IF(E7=0,"Check # of Passes",IF(ISTEXT(E7),"Check # of Passes",IF(E8="","Enter water rate",IF(E8=0,"Check water rate",IF(ISTEXT(E8),"Check water rate",IF(E9="","Enter area to treat",IF(E9=0,"Check area to treat",IF(ISTEXT(E9),"Check area to treat",IF(ISERROR(E11*E7),"Check entres",(E11*E7))))))))))))))))))))))))))))))))))
F12F12=IF(AND(E6>0,F6>0),"Enter ONLY mls or lts",IF(AND(E6>0,F7>0),"Enter ONLY mls or lts",IF(AND(E6>0,F8>0),"Enter ONLY mls or lts",IF(AND(E6>0,F9>0),"Enter ONLY mls or lts",IF(AND(E7>0,F6>0),"Enter ONLY mls or lts",IF(AND(E7>0,F7>0),"Enter ONLY mls or lts",IF(AND(E7>0,F8>0),"Enter ONLY mls or lts",IF(AND(E7>0,F9>0),"Enter ONLY mls or lts",IF(AND(E8>0,F6>0),"Enter ONLY mls or lts",IF(AND(E8>0,F7>0),"Enter ONLY mls or lts",IF(AND(E8>0,F8>0),"Enter ONLY mls or lts",IF(AND(E8>0,F9>0),"Enter ONLY mls or lts",IF(AND(E9>0,F6>0),"Enter ONLY mls or lts",IF(AND(E9>0,F7>0),"Enter ONLY mls or lts",IF(AND(E9>0,F8>0),"Enter ONLY mls or lts",IF(AND(E9>0,F9>0),"Enter ONLY mls or lts",IF(E6<>"","",IF(E7<>"","",IF(E8<>"","",IF(E9<>"","",IF(F6="","Enter dose rate",IF(F6=0,"Check dose rate",IF(ISTEXT(F6),"Check dose rate",IF(F7="","Enter # of Passes",IF(F7=0,"Check # of Passes",IF(ISTEXT(F7),"Check # of Passes",IF(F8="","Enter water rate",IF(F8=0,"Check water rate",IF(ISTEXT(F8),"Check water rate",IF(F9="","Enter area to treat",IF(F9=0,"Check area to treat",IF(ISTEXT(F9),"Check area to treat",IF(ISERROR(F11*F7),"Check entres",(F11*F7))))))))))))))))))))))))))))))))))
E13E13=IF(E4=0,"",IF(E6=0,"",IF(E7=0,"",IF(E8=0,"",IF(E9=0,"",IF(ISTEXT(E10),"dd",IF((E8*E9)/(E4*1000)<0.0001,"Check entres",IF(ISNUMBER(E10),(E8*E9)/(E4*1000)))))))))
F13F13=IF(E4=0,"",IF(F6=0,"",IF(F7=0,"",IF(F8=0,"",IF(F9=0,"",IF(ISTEXT(F10),"dd",IF(((F9*10000)*F8)/(E4*1000)/10<0.00001,"Check entres",IF(ISNUMBER(F10),(F9*10000)*F8)/(E4*1000)/10)))))))
E15E15=IF(ISTEXT(E10),"",IF(ISTEXT(E11),"Check entres",IF(ISTEXT(E12),"Check entres",IF(ISTEXT(E13),"Check entres",IF(TRUNC(E13)=0,"No Full tank",IF(TRUNC(E13)>0,(TRUNC(E13)/TRUNC(E13)*(E4*1000)/E8)*E10,"tt")/1000)))))
F15F15=IF(ISTEXT(F10),"",IF(ISTEXT(F11),"Check entres",IF(ISTEXT(F12),"Check entres",IF(ISTEXT(F13),"Check entres",IF(TRUNC(F13)=0,"No Full tank",IF(TRUNC(F13)>0,(TRUNC(F13)/TRUNC(F13)*(E4*10000)/F8)*F10,"tt")/1000)))))
E16E16=IF(ISTEXT(E10),"",IF(ISTEXT(E11),"Check entres",IF(ISTEXT(E12),"Check entres",IF(ISTEXT(E13),"Check entres",IF(TRUNC(E13)=0,"No Full tank",IF(TRUNC(E13)>0,(TRUNC(E13)/TRUNC(E13)*(E4*1000)/E8)*E11,"tt")/1000)))))
F16F16=IF(ISTEXT(F10),"",IF(ISTEXT(F11),"Check entres",IF(ISTEXT(F12),"Check entres",IF(ISTEXT(F13),"Check entres",IF(TRUNC(F13)=0,"No Full tank",IF(TRUNC(F13)>0,(TRUNC(F13)/TRUNC(F13)*(E4*10000)/F8)*F11,"tt")/1000)))))
E17E17=IF(ISTEXT(E10),"",IF(ISTEXT(E11),"Check entres",IF(ISTEXT(E12),"Check entres",IF(ISTEXT(E13),"Check entres",IF(E13-TRUNC(E13)=0,"No PART Tank",((E13-TRUNC(E13))*(E4*1000)/1000))))))
F17F17=IF(ISTEXT(F10),"",IF(ISTEXT(F11),"Check entres",IF(ISTEXT(F12),"Check entres",IF(ISTEXT(F13),"Check entres",IF(F13-TRUNC(F13)=0,"No PART Tank",((F13-TRUNC(F13))*(E4*1000)/1000))))))
E18E18=IF(ISTEXT(E10),"",IF(ISTEXT(E11),"Check entres",IF(ISTEXT(E12),"Check entres",IF(ISTEXT(E13),"Check entres",IF(E13-TRUNC(E13)=0,"No PART Tank",((E13-TRUNC(E13))*((E4*1000)/E8))*E10)))))
F18F18=IF(ISTEXT(F10),"",IF(ISTEXT(F11),"Check entres",IF(ISTEXT(F12),"Check entres",IF(ISTEXT(F13),"Check entres",IF(F13-TRUNC(F13)=0,"No PART Tank",((F13-TRUNC(F13))*((E4*10000)/F8))*F10)))))
E19E19=IF(ISTEXT(E10),"",IF(ISTEXT(E11),"Check entres",IF(ISTEXT(E12),"Check entres",IF(ISTEXT(E13),"Check entres",IF(E13-TRUNC(E13)=0,"No PART Tank",((E13-TRUNC(E13))*((E4)/E8))*E11)))))
F19F19=IF(ISTEXT(F10),"",IF(ISTEXT(F11),"Check entres",IF(ISTEXT(F12),"Check entres",IF(ISTEXT(F13),"Check entres",IF(F13-TRUNC(F13)=0,"No PART Tank",((F13-TRUNC(F13))*((E4*10000)/(F8*1000))*F11))))))
E24E24=IF(F6<>"","",IF(F7<>"","",IF(F8<>"","",IF(F9<>"","",IF(E6="","Enter dose rate",IF(E6=0,"Check dose rate",IF(ISTEXT(E6),"Check dose rate",IF(E7="","Enter # passes",IF(E7=0,"Check # passes",IF(ISTEXT(E7),"Check # passes",IF(E8="","Enter water rate",IF(E8=0,"Check water rate",IF(ISTEXT(E8),"Check water rate",IF(E9="","Enter area to treat",IF(E9=0,"Check area to treat",IF(ISTEXT(E9),"Check area to treat",IF(E22="","Enter product size",IF(E22=0,"Check product size",IF(ISTEXT(E22),"Check product size",IF(E23="","Enter product cost",IF(E23=0,"Check product cost",IF(ISTEXT(E23),"Check product cost",(E23*100)/(E22*1000)))))))))))))))))))))))
F24F24=IF(E6<>"","",IF(E7<>"","",IF(E8<>"","",IF(E9<>"","",IF(F6="","Enter dose rate",IF(F6=0,"Check dose rate",IF(ISTEXT(F6),"Check dose rate",IF(F7="","Enter # passes",IF(F7=0,"Check # passes",IF(ISTEXT(F7),"Check # passes",IF(F8="","Enter water rate",IF(F8=0,"Check water rate",IF(ISTEXT(F8),"Check water rate",IF(F9="","Enter area to treat",IF(F9=0,"Check area to treat",IF(ISTEXT(F9),"Check area to treat",IF(E22="","Enter product size",IF(E22=0,"Check product size",IF(ISTEXT(E22),"Check product size",IF(E23="","Enter product cost",IF(E23=0,"Check product cost",IF(ISTEXT(E23),"Check product cost",(E23/E22)))))))))))))))))))))))
E25E25=IF(F6<>"","",IF(F7<>"","",IF(F8<>"","",IF(F9<>"","",IF(E6="","Enter dose rate",IF(E6=0,"Check dose rate",IF(ISTEXT(E6),"Check dose rate",IF(E7="","Enter # passes",IF(E7=0,"Check # passes",IF(ISTEXT(E7),"Check # passes",IF(E8="","Enter water rate",IF(E8=0,"Check water rate",IF(ISTEXT(E8),"Check water rate",IF(E9="","Enter area to treat",IF(E9=0,"Check area to treat",IF(ISTEXT(E9),"Check area to treat",IF(E22="","Enter product size",IF(E22=0,"Check product size",IF(ISTEXT(E22),"Check product size",IF(E23="","Enter product cost",IF(E23=0,"Check product cost",IF(ISTEXT(E23),"Check product cost",(E6*E9)))))))))))))))))))))))
F25F25=IF(E6<>"","",IF(E7<>"","",IF(E8<>"","",IF(E9<>"","",IF(F6="","Enter dose rate",IF(F6=0,"Check dose rate",IF(ISTEXT(F6),"Check dose rate",IF(F7="","Enter # passes",IF(F7=0,"Check # passes",IF(ISTEXT(F7),"Check # passes",IF(F8="","Enter water rate",IF(F8=0,"Check water rate",IF(ISTEXT(F8),"Check water rate",IF(F9="","Enter area to treat",IF(F9=0,"Check area to treat",IF(ISTEXT(F9),"Check area to treat",IF(E22="","Enter product size",IF(E22=0,"Check product size",IF(ISTEXT(E22),"Check product size",IF(E23="","Enter product cost",IF(E23=0,"Check product cost",IF(ISTEXT(E23),"Check product cost",(F6*1000)/10000)*(F9*10000)/1000)))))))))))))))))))))
E26E26=IF(F6<>"","",IF(F7<>"","",IF(F8<>"","",IF(F9<>"","",IF(E6="","Enter dose rate",IF(E6=0,"Check dose rate",IF(ISTEXT(E6),"Check dose rate",IF(E7="","Enter # passes",IF(E7=0,"Check # passes",IF(ISTEXT(E7),"Check # passes",IF(E8="","Enter water rate",IF(E8=0,"Check water rate",IF(ISTEXT(E8),"Check water rate",IF(E9="","Enter area to treat",IF(E9=0,"Check area to treat",IF(ISTEXT(E9),"Check area to treat",IF(E22="","Enter product size",IF(E22=0,"Check product size",IF(ISTEXT(E22),"Check product size",IF(E23="","Enter product cost",IF(E23=0,"Check product cost",IF(ISTEXT(E23),"Check product cost",((E24*E25/100))))))))))))))))))))))))
F26F26=IF(E6<>"","",IF(E7<>"","",IF(E8<>"","",IF(E9<>"","",IF(F6="","Enter dose rate",IF(F6=0,"Check dose rate",IF(ISTEXT(F6),"Check dose rate",IF(F7="","Enter # passes",IF(F7=0,"Check # passes",IF(ISTEXT(F7),"Check # passes",IF(F8="","Enter water rate",IF(F8=0,"Check water rate",IF(ISTEXT(F8),"Check water rate",IF(F9="","Enter area to treat",IF(F9=0,"Check area to treat",IF(ISTEXT(F9),"Check area to treat",IF(E22="","Enter product size",IF(E22=0,"Check product size",IF(ISTEXT(E22),"Check product size",IF(E23="","Enter product cost",IF(E23=0,"Check product cost",IF(ISTEXT(E23),"Check product cost",(F24*F25)))))))))))))))))))))))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
F15:F19Expression=E$16<>""textYES
F15:F19Expression=E$17<>""textYES
E15:E19Expression=F$16<>""textYES
E15:E19Expression=F$17<>""textYES
E15:E19Expression=F$18<>""textYES
F15:F19Expression=E$18<>""textYES
E13:F13Expression=ISTEXT(E13:F13)textYES
E15:E19Expression=F$19<>""textYES
F15:F19Expression=E$19<>""textYES
E15:E16Expression=ISTEXT(E15:E16)textYES
F6:F12Expression=E$16<>""textYES
E17:E19Expression=ISTEXT(E17:E19)textYES
E15:E19Expression=E$13<>""textYES
F6:F12Expression=E$17<>""textYES
F6:F12Expression=E$18<>""textYES
F15:F16Expression=ISTEXT(F15:F16)textYES
F17:F19Expression=ISTEXT(F17:F19)textNO
F15:F19Expression=F$13<>""textYES
F6:F12Expression=E$19<>""textYES
E6:E12Expression=F$16<>""textYES
E6:E12Expression=F$17<>""textYES
E6:E12Expression=F$18<>""textYES
E6:E12Expression=F$19<>""textYES
E10:F12Expression=ISNUMBER(E10:F12)textYES
E13:F13Expression=ISNUMBER(E13:F13)textYES
E24:E25Expression=F$21<>""textYES
F24:F25Expression=E$21<>""textYES
E24:E25Expression=ISNUMBER(E24:E26)textYES
F24:F25Expression=ISNUMBER(F24:F26)textYES
F26Expression=E$21<>""textYES
F26Expression=ISNUMBER(F26:F27)textYES
E26Expression=F$21<>""textYES
E26Expression=ISNUMBER(E26:E27)textYES
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Sort by date Sort by votes
i'm not following - sorry

Lets take E17 , in the text of the POST
IF((E13-TRUNC(E13))*(E4*1000)/1000<1,"xx",((E13-TRUNC(E13))*(E4*1000)/1000)))))))

If above (eg 1.00) simply displayed as 1.00, but if it’s below (eg 0.10) it then needs to convert "lts" to "mls", so is displayed as 100.
Click to expand...

IF(E13-TRUNC(E13))*(E4*1000)/1000)<1,
So we need to multiply by 100 as now we have MIS , because the result is below 1 and also add "MIS" - where are we changing its to mis ?
or are we adding that to the result - so
E13-TRUNC(E13))*(E4*1000)/1000)*100&" mis"

then
IF((E13-TRUNC(E13))*(E4*1000)/1000<1,E13-TRUNC(E13))*(E4*1000)/1000)*100&" mis",((E13-TRUNC(E13))*(E4*1000)/1000)&" its" )))))))
Not sure on your brackets
BUT that will turn the result into text numbers and so can not be used in other calculations without conversion

maybe i'm completely misunderstanding

I have only changed E17 , no idea if other calculations are impacted
or even if this is what you meant
=IF(ISTEXT(E10),"",IF(ISTEXT(E11),"Check entres",IF(ISTEXT(E12),"Check entres",IF(ISTEXT(E13),"Check entres",IF(E13-TRUNC(E13)=0,"No PART Tank",IF(((E13-TRUNC(E13))*(E4*1000)/1000)<1,((E13-TRUNC(E13))*(E4*1000)/1000)&" mis",((E13-TRUNC(E13))*(E4*1000)/1000)&" its"))))))

Book1
ABCDEFGHIJK
1
2
3
4Tank size in ltrs25E4=Input Tank size in ltrs (25 or 15)
5ml / meterltr/ha
6Product Dose rate1E6:E9=Data Input Area
7# of Passes1
8Total Volume Rate / m250For simple testing:-
9Size of Area to treat1to affect E17:E19, change value in E9 to
10Product Dose rate / Pass1  anything (eg 20-1100)
11Water Dose rate / Pass49 
12Total Water rate49 
13Tanks required0.002 
14
15Product per FULL tankNo Full tank 
16Water per FULL tankNo Full tank 
17PART tank TOTAL0.05 mis 
18Product FOR PART tank1 
19Water FOR PART tank0.049 
Sheet1
Cell Formulas
RangeFormula
E10E10=IF(AND(E6>0,F6>0),"Enter ONLY mls or lts",IF(AND(E6>0,F7>0),"Enter ONLY mls or lts",IF(AND(E6>0,F8>0),"Enter ONLY mls or lts",IF(AND(E6>0,F9>0),"Enter ONLY mls or lts",IF(AND(E7>0,F6>0),"Enter ONLY mls or lts",IF(AND(E7>0,F7>0),"Enter ONLY mls or lts",IF(AND(E7>0,F8>0),"Enter ONLY mls or lts",IF(AND(E7>0,F9>0),"Enter ONLY mls or lts",IF(AND(E8>0,F6>0),"Enter ONLY mls or lts",IF(AND(E8>0,F7>0),"Enter ONLY mls or lts",IF(AND(E8>0,F8>0),"Enter ONLY mls or lts",IF(AND(E8>0,F9>0),"Enter ONLY mls or lts",IF(AND(E9>0,F6>0),"Enter ONLY mls or lts",IF(AND(E9>0,F7>0),"Enter ONLY mls or lts",IF(AND(E9>0,F8>0),"Enter ONLY mls or lts",IF(AND(E9>0,F9>0),"Enter ONLY mls or lts",IF(F6<>"","",IF(F7<>"","",IF(F8<>"","",IF(F9<>"","",IF(E6="","Enter dose rate ",IF(E6=0,"Check dose rate",IF(ISTEXT(E6),"Check dose rate",IF(E7="","Enter # of Passes",IF(E7=0,"Check # of Passes",IF(ISTEXT(E7),"Check # of Passes",IF(E8="","Enter water rate",IF(E8=0,"Check water rate",IF(ISTEXT(E8),"Check water rate",IF(E9="","Enter area to treat",IF(E9=0,"Check area to treat",IF(ISTEXT(E9),"Check area to treat",IF((E6/E7)=E8,"kk",(E6/E7))))))))))))))))))))))))))))))))))
F10F10=IF(AND(E6>0,F6>0),"Enter ONLY mls or lts",IF(AND(E6>0,F7>0),"Enter ONLY mls or lts",IF(AND(E6>0,F8>0),"Enter ONLY mls or lts",IF(AND(E6>0,F9>0),"Enter ONLY mls or lts",IF(AND(E7>0,F6>0),"Enter ONLY mls or lts",IF(AND(E7>0,F7>0),"Enter ONLY mls or lts",IF(AND(E7>0,F8>0),"Enter ONLY mls or lts",IF(AND(E7>0,F9>0),"Enter ONLY mls or lts",IF(AND(E8>0,F6>0),"Enter ONLY mls or lts",IF(AND(E8>0,F7>0),"Enter ONLY mls or lts",IF(AND(E8>0,F8>0),"Enter ONLY mls or lts",IF(AND(E8>0,F9>0),"Enter ONLY mls or lts",IF(AND(E9>0,F6>0),"Enter ONLY mls or lts",IF(AND(E9>0,F7>0),"Enter ONLY mls or lts",IF(AND(E9>0,F8>0),"Enter ONLY mls or lts",IF(AND(E9>0,F9>0),"Enter ONLY mls or lts",IF(E6<>"","",IF(E7<>"","",IF(E8<>"","",IF(E9<>"","",IF(F6="","Enter dose rate",IF(F6=0,"Check dose rate",IF(ISTEXT(F6),"Check dose rate",IF(F7="","Enter # of Passes",IF(F7=0,"Check # of Passes",IF(ISTEXT(F7),"Check # of Passes",IF(F8="","Enter water rate",IF(F8=0,"Check water rate",IF(ISTEXT(F8),"Check water rate",IF(F9="","Enter area to treat",IF(F9=0,"Check area to treat",IF(ISTEXT(F9),"Check area to treat",IF(F6/F7=F8,"kk",(((F6*1000)/F7)/1000)/10)))))))))))))))))))))))))))))))))
E11E11=IF(AND(E6>0,F6>0),"Enter ONLY mls or lts",IF(AND(E6>0,F7>0),"Enter ONLY mls or lts",IF(AND(E6>0,F8>0),"Enter ONLY mls or lts",IF(AND(E6>0,F9>0),"Enter ONLY mls or lts",IF(AND(E7>0,F6>0),"Enter ONLY mls or lts",IF(AND(E7>0,F7>0),"Enter ONLY mls or lts",IF(AND(E7>0,F8>0),"Enter ONLY mls or lts",IF(AND(E7>0,F9>0),"Enter ONLY mls or lts",IF(AND(E8>0,F6>0),"Enter ONLY mls or lts",IF(AND(E8>0,F7>0),"Enter ONLY mls or lts",IF(AND(E8>0,F8>0),"Enter ONLY mls or lts",IF(AND(E8>0,F9>0),"Enter ONLY mls or lts",IF(AND(E9>0,F6>0),"Enter ONLY mls or lts",IF(AND(E9>0,F7>0),"Enter ONLY mls or lts",IF(AND(E9>0,F8>0),"Enter ONLY mls or lts",IF(AND(E9>0,F9>0),"Enter ONLY mls or lts",IF(F6<>"","",IF(F7<>"","",IF(F8<>"","",IF(F9<>"","",IF(E6="","Enter dose rate",IF(E6=0,"Check dose rate",IF(ISTEXT(E6),"Check dose rate",IF(E7="","Enter # of Passes",IF(E7=0,"Check # of Passes",IF(ISTEXT(E7),"Check # of Passes",IF(E8="","Enter water rate",IF(E8=0,"Check water rate",IF(ISTEXT(E8),"Check water rate",IF(E9="","Enter area to treat",IF(E9=0,"Check area to treat",IF(ISTEXT(E9),"Check area to treat",IF(E8-E6/E7<=0,"Check entres",(E8-E6)/E7)))))))))))))))))))))))))))))))))
F11F11=IF(AND(E6>0,F6>0),"Enter ONLY mls or lts",IF(AND(E6>0,F7>0),"Enter ONLY mls or lts",IF(AND(E6>0,F8>0),"Enter ONLY mls or lts",IF(AND(E6>0,F9>0),"Enter ONLY mls or lts",IF(AND(E7>0,F6>0),"Enter ONLY mls or lts",IF(AND(E7>0,F7>0),"Enter ONLY mls or lts",IF(AND(E7>0,F8>0),"Enter ONLY mls or lts",IF(AND(E7>0,F9>0),"Enter ONLY mls or lts",IF(AND(E8>0,F6>0),"Enter ONLY mls or lts",IF(AND(E8>0,F7>0),"Enter ONLY mls or lts",IF(AND(E8>0,F8>0),"Enter ONLY mls or lts",IF(AND(E8>0,F9>0),"Enter ONLY mls or lts",IF(AND(E9>0,F6>0),"Enter ONLY mls or lts",IF(AND(E9>0,F7>0),"Enter ONLY mls or lts",IF(AND(E9>0,F8>0),"Enter ONLY mls or lts",IF(AND(E9>0,F9>0),"Enter ONLY mls or lts",IF(E6<>"","",IF(E7<>"","",IF(E8<>"","",IF(E9<>"","",IF(F6="","Enter dose rate",IF(F6=0,"Check dose rate",IF(ISTEXT(F6),"Check dose rate",IF(F7="","Enter # of Passes",IF(F7=0,"Check # of Passes",IF(ISTEXT(F7),"Check # of Passes",IF(F8="","Enter water rate",IF(F8=0,"Check water rate",IF(ISTEXT(F8),"Check water rate",IF(F9="","Enter area to treat",IF(F9=0,"Check area to treat",IF(ISTEXT(F9),"Check area to treat",IF(((F8*1000)/10000)-((F6*1000)/10000)/F7<=0,"Check entres",(((F8*1000)/10000)-((F6*1000)/10000)/F7))))))))))))))))))))))))))))))))))
E12E12=IF(AND(E6>0,F6>0),"Enter ONLY mls or lts",IF(AND(E6>0,F7>0),"Enter ONLY mls or lts",IF(AND(E6>0,F8>0),"Enter ONLY mls or lts",IF(AND(E6>0,F9>0),"Enter ONLY mls or lts",IF(AND(E7>0,F6>0),"Enter ONLY mls or lts",IF(AND(E7>0,F7>0),"Enter ONLY mls or lts",IF(AND(E7>0,F8>0),"Enter ONLY mls or lts",IF(AND(E7>0,F9>0),"Enter ONLY mls or lts",IF(AND(E8>0,F6>0),"Enter ONLY mls or lts",IF(AND(E8>0,F7>0),"Enter ONLY mls or lts",IF(AND(E8>0,F8>0),"Enter ONLY mls or lts",IF(AND(E8>0,F9>0),"Enter ONLY mls or lts",IF(AND(E9>0,F6>0),"Enter ONLY mls or lts",IF(AND(E9>0,F7>0),"Enter ONLY mls or lts",IF(AND(E9>0,F8>0),"Enter ONLY mls or lts",IF(AND(E9>0,F9>0),"Enter ONLY mls or lts",IF(F6<>"","",IF(F7<>"","",IF(F8<>"","",IF(F9<>"","",IF(E6="","Enter dose rate",IF(E6=0,"Check dose rate",IF(ISTEXT(E6),"Check dose rate",IF(E7="","Enter # of Passes",IF(E7=0,"Check # of Passes",IF(ISTEXT(E7),"Check # of Passes",IF(E8="","Enter water rate",IF(E8=0,"Check water rate",IF(ISTEXT(E8),"Check water rate",IF(E9="","Enter area to treat",IF(E9=0,"Check area to treat",IF(ISTEXT(E9),"Check area to treat",IF(ISERROR(E11*E7),"Check entres",(E11*E7))))))))))))))))))))))))))))))))))
F12F12=IF(AND(E6>0,F6>0),"Enter ONLY mls or lts",IF(AND(E6>0,F7>0),"Enter ONLY mls or lts",IF(AND(E6>0,F8>0),"Enter ONLY mls or lts",IF(AND(E6>0,F9>0),"Enter ONLY mls or lts",IF(AND(E7>0,F6>0),"Enter ONLY mls or lts",IF(AND(E7>0,F7>0),"Enter ONLY mls or lts",IF(AND(E7>0,F8>0),"Enter ONLY mls or lts",IF(AND(E7>0,F9>0),"Enter ONLY mls or lts",IF(AND(E8>0,F6>0),"Enter ONLY mls or lts",IF(AND(E8>0,F7>0),"Enter ONLY mls or lts",IF(AND(E8>0,F8>0),"Enter ONLY mls or lts",IF(AND(E8>0,F9>0),"Enter ONLY mls or lts",IF(AND(E9>0,F6>0),"Enter ONLY mls or lts",IF(AND(E9>0,F7>0),"Enter ONLY mls or lts",IF(AND(E9>0,F8>0),"Enter ONLY mls or lts",IF(AND(E9>0,F9>0),"Enter ONLY mls or lts",IF(E6<>"","",IF(E7<>"","",IF(E8<>"","",IF(E9<>"","",IF(F6="","Enter dose rate",IF(F6=0,"Check dose rate",IF(ISTEXT(F6),"Check dose rate",IF(F7="","Enter # of Passes",IF(F7=0,"Check # of Passes",IF(ISTEXT(F7),"Check # of Passes",IF(F8="","Enter water rate",IF(F8=0,"Check water rate",IF(ISTEXT(F8),"Check water rate",IF(F9="","Enter area to treat",IF(F9=0,"Check area to treat",IF(ISTEXT(F9),"Check area to treat",IF(ISERROR(F11*F7),"Check entres",(F11*F7))))))))))))))))))))))))))))))))))
E13E13=IF(E4=0,"",IF(E6=0,"",IF(E7=0,"",IF(E8=0,"",IF(E9=0,"",IF(ISTEXT(E10),"dd",IF((E8*E9)/(E4*1000)<0.0001,"Check entres",IF(ISNUMBER(E10),(E8*E9)/(E4*1000)))))))))
F13F13=IF(E4=0,"",IF(F6=0,"",IF(F7=0,"",IF(F8=0,"",IF(F9=0,"",IF(ISTEXT(F10),"dd",IF(((F9*10000)*F8)/(E4*1000)/10<0.00001,"Check entres",IF(ISNUMBER(F10),(F9*10000)*F8)/(E4*1000)/10)))))))
E15E15=IF(ISTEXT(E10),"",IF(ISTEXT(E11),"Check entres",IF(ISTEXT(E12),"Check entres",IF(ISTEXT(E13),"Check entres",IF(TRUNC(E13)=0,"No Full tank",IF(TRUNC(E13)>0,(TRUNC(E13)/TRUNC(E13)*(E4*1000)/E8)*E10,"tt")/1000)))))
F15F15=IF(ISTEXT(F10),"",IF(ISTEXT(F11),"Check entres",IF(ISTEXT(F12),"Check entres",IF(ISTEXT(F13),"Check entres",IF(TRUNC(F13)=0,"No Full tank",IF(TRUNC(F13)>0,(TRUNC(F13)/TRUNC(F13)*(E4*10000)/F8)*F10,"tt")/1000)))))
E16E16=IF(ISTEXT(E10),"",IF(ISTEXT(E11),"Check entres",IF(ISTEXT(E12),"Check entres",IF(ISTEXT(E13),"Check entres",IF(TRUNC(E13)=0,"No Full tank",IF(TRUNC(E13)>0,(TRUNC(E13)/TRUNC(E13)*(E4*1000)/E8)*E11,"tt")/1000)))))
F16F16=IF(ISTEXT(F10),"",IF(ISTEXT(F11),"Check entres",IF(ISTEXT(F12),"Check entres",IF(ISTEXT(F13),"Check entres",IF(TRUNC(F13)=0,"No Full tank",IF(TRUNC(F13)>0,(TRUNC(F13)/TRUNC(F13)*(E4*10000)/F8)*F11,"tt")/1000)))))
E17E17=IF(ISTEXT(E10),"",IF(ISTEXT(E11),"Check entres",IF(ISTEXT(E12),"Check entres",IF(ISTEXT(E13),"Check entres",IF(E13-TRUNC(E13)=0,"No PART Tank",IF(((E13-TRUNC(E13))*(E4*1000)/1000)<1,((E13-TRUNC(E13))*(E4*1000)/1000)&" mis",((E13-TRUNC(E13))*(E4*1000)/1000)&" its"))))))
F17F17=IF(ISTEXT(F10),"",IF(ISTEXT(F11),"Check entres",IF(ISTEXT(F12),"Check entres",IF(ISTEXT(F13),"Check entres",IF(F13-TRUNC(F13)=0,"No PART Tank",((F13-TRUNC(F13))*(E4*1000)/1000))))))
E18E18=IF(ISTEXT(E10),"",IF(ISTEXT(E11),"Check entres",IF(ISTEXT(E12),"Check entres",IF(ISTEXT(E13),"Check entres",IF(E13-TRUNC(E13)=0,"No PART Tank",((E13-TRUNC(E13))*((E4*1000)/E8))*E10)))))
F18F18=IF(ISTEXT(F10),"",IF(ISTEXT(F11),"Check entres",IF(ISTEXT(F12),"Check entres",IF(ISTEXT(F13),"Check entres",IF(F13-TRUNC(F13)=0,"No PART Tank",((F13-TRUNC(F13))*((E4*10000)/F8))*F10)))))
E19E19=IF(ISTEXT(E10),"",IF(ISTEXT(E11),"Check entres",IF(ISTEXT(E12),"Check entres",IF(ISTEXT(E13),"Check entres",IF(E13-TRUNC(E13)=0,"No PART Tank",((E13-TRUNC(E13))*((E4)/E8))*E11)))))
F19F19=IF(ISTEXT(F10),"",IF(ISTEXT(F11),"Check entres",IF(ISTEXT(F12),"Check entres",IF(ISTEXT(F13),"Check entres",IF(F13-TRUNC(F13)=0,"No PART Tank",((F13-TRUNC(F13))*((E4*10000)/(F8*1000))*F11))))))
 
Upvote 0
Solution
Thanks for response etaf
Sorry for the confusion!
As a by the way, mls is MLS just in lowercase, same for LTS

Your suggestion is in essence what I was trying to do.
Excel Formula: 
=IF(ISTEXT(E10),"",IF(ISTEXT(E11),"Check entres",IF(ISTEXT(E12),"Check entres",IF(ISTEXT(E13),"Check entres",IF(E13-TRUNC(E13)=0,"No PART Tank",IF(((E13-TRUNC(E13))*(E4*1000)/1000)<1,((E13-TRUNC(E13))*(E4*1000)/1000)&" mis",((E13-TRUNC(E13))*(E4*1000)/1000)&" its"))))))

Using >> IF(((E13-TRUNC(E13))*(E4*1000)/1000)<1 is to test if calculated value resulted in number of say eg 0.100 (which is 0.100 LTS), if so want to convert that to 100 MLS.

The reverse argument of >> IF(((E13-TRUNC(E13))*(E4*1000)/1000)<1 is that the value is eg 1.00 (which is 1.00 LTS), which I don’t want to convert MLS.

I need to look again at the last division, plus how going to work around results being as text.
Will come back to you.
 
Upvote 0
OK, sorry wasn't looking at the letters that closely , but you do get the idea that you can do an IF test and Concatenate text after the result now as show
 
Upvote 0
Took out highlighted division below, so that in your example where E17 reads 0.05 mls it would now read 50 mls which is what wanted
IF(((E13-TRUNC(E13))*(E4*1000)/1000)<1,((E13-TRUNC(E13))*(E4*1000)/1000)&" mls",((E13-TRUNC(E13))*(E4*1000)/1000)&" lts"))))))

E17 being text not a problem as no calculations are dependent on it.
Just have to tweak some existing CF in E17 to deal with it being text.

Many thanks for your help
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
Question regarding Checkboxes changing formulas in cells?
2
Replies
12
Views
539
Micron
M
O
  • Solved
How to calculate annual average growth (i.e., price appreciation) rate for an investment portfolio.
Replies
3
Views
144
kvsrinivasamurthy
K
M
  • Question Question
Mock Finance sheet
Replies
3
Views
341
Marvo
M
K
  • Question Question
Populate With Criteria
Replies
3
Views
85
Peter_SSs
Peter_SSs
S
  • Question Question
Calculate product percentage impact on total percentage
Replies
1
Views
276
etaf
etaf

Forum statistics

Threads
1,224,823
Messages
6,181,182
Members
453,021
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

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