#Value problem

[julhs]

These 2 formula work as expected; individually.

=IF(AND(RIGHT(D32,FIND(" ",D32)-1)="kg",TRUNC(D23)>0),TRUNC(D23)*(D14*1000)/D18)*(LEFT(D20,FIND(" ",D20))/1000)&" "&F26
=IF(AND(RIGHT(D32,FIND(" ",D32)+1)="lts",TRUNC(D23)>0),TRUNC(D23)*(D14*1000)/D18)*(LEFT(D20,FIND(" ",D20))/1000)&" "&F25

But for the life of me I can’t combine the 2 without running into issues.

Would very much appreciate help on it!!

 

[Joe4]

I think it would be helpful to us if you can show us some sample data, and then what your expected values are.
Be sure to include your different scenarios.

 

[julhs]

Sorry Joe.
Maybe not helping my question or you solve it, but uploading it as “XL2B” is not an option.
So hope this may help to follow the formula?

D32 for instance could be eg: 5 kg or 7 lts (stored as number with custom formatting)
D23 is stored as a number, anything from 0.0001 and upwards
D14 = 25 stored as a number (lts, hence the * 1000)
D18 = eg, 2 (Stored as a number)
D20 = 1 ml / m2, (stored as text)
F26 = “Kg” (stored as General)
F25 = “lts” (stored as General)

So,
If(RIGHT(D32,FIND(" ",D32)-1)=” kg",... continue with calculation. Result is the calculation, with added “kg” eg, 5 kg
If(RIGHT(D32,FIND(" ",D32)+1)=” lts” ... continue with calculation. Result is the calculation, with added “lts” eg, 7 lts

As I said before, the 2 formulas I posted work perfectly OK as stand along individual formulas and give the results that are expected, eg 5 kg, 7 lts. What I’m struggling with is combining them into one single formula without #Value errors!!

Is it my imagination that when using “If”+“TRUNC”+”Find” requires a slightly different logic/syntax ?

 

[Joe4]

I am trying to make sense of the logic in your formula. Each formula by itself does not look complete, as I do not think there is any FALSE clause in each IF statement.

It would be a great help if you show some sample data along with your expected results, and explain what the logic behind the formula.
Even if you cannot use XL2BB, just pasting some images of your data would be helpful.

 

[julhs]

Your comment “Each formula by itself does not look complete” is exactly one of the things I had problems over.
Ordinarily, if you use an “IF” statement you need; the if “True” part and an if “False” side to the argument
However, if I try to adding an if “False” part to the below, it will not be excepted; I get “Formula contains an error” message despite everything I’ve tried!!

=IF(AND(RIGHT(D32,FIND(" ",D32)-1)="kg",TRUNC(D23)>0),TRUNC(D23)*(D14*1000)/D18)*(LEFT(D20,FIND(" ",D20))/1000)&" "&F26

But despite it looking incomplete, formulas individually give me the result I expect. “Formula calculation + ” kg” = eg 5 kg
But your saying formula does not look complete is probably principal reason behind my not being able to combine the 2 “If’” arguments into the one formula in the way you would expect.

I’ll endeavour to knock something up tomorrow that I can put in Xl2bb

 

[julhs]

It’s now sorted.
So often is the case I struggling with the parentheses amongst other things, school boy mistakes.
With a fresher head I was able to see where I was wrong.
Just for reference:

=IF(AND(RIGHT(D32,FIND(" ",D32)-1)="kg",TRUNC(D23)>0),TRUNC(D23)*(D14*1000)/D18*(LEFT(D20,FIND(" ",D20)))/1000&" "&F26,TRUNC(D23)*(D14*1000)/D18*(LEFT(D20,FIND(" ",D20)))/1000&" "&F25)

Refer to my post #3 for cell values

 

[Joe4]

Excellent!

Glad you got it figured out.