9 Replies Latest reply on Jan 19, 2009 4:44 AM by Mirel

# Calculate Lenght*Width*Height even if one of them is null

### Title

Calculate Lenght*Width*Height even if one of them is null

### Post

Ciao!

This is my first post here so... be kind please (not necessary actually)

So here is my problem, actually my firs in a very very long list (you're probably see all of those, I just want to make one thing at a time...)

I have 3 fields/cells (don't know the english name, stupid italian user:)) wich are width, lenght and height. I need the "total" field to calculate width*lenght*heght, even if one of those are null.

What I do now is Width*Lenght*Height but if one of these are null, then the result is 0 ( every number * zero equals zero...)

Hope somebody could help, thanks!

• ###### 1. Re: Calculate Lenght*Width*Height even if one of them is null
You could modify the zeros to 1
`if( width ; width ; 1 ) * if( length ; length ; 1) * if( height ; height; 1 )`
if that is what you want.

Jens

• ###### 2. Re: Calculate Lenght*Width*Height even if one of them is null

Hi Mirel

another way is this:

Evaluate ( Substitute ( List ( larghezza ; lunghezza ; altezza ) ; ¶ ; "*" ) )

• ###### 3. Re: Calculate Lenght*Width*Height even if one of them is null

Hi Jens

your calculation will fail if you force to evaluate even if fields are empty ( result 1 )

• ###### 4. Re: Calculate Lenght*Width*Height even if one of them is null
You mean in case that all fields are empty? I see.

Your solution is smarter anyway ;)

Jens

• ###### 5. Re: Calculate Lenght*Width*Height even if one of them is null

So, the problem about using "if( width ; width ; 1 ) * if( length ; length ; 1) * if( height ; height; 1 )" is that if somebody puts in to the fields 0-0-0 then the result is 1.

Instead, the problem with using "Evaluate ( Substitute ( List ( larghezza ; lunghezza ; altezza ) ; ¶ ; "*" ) )" is that if somebody puts in the fields for example 2-5-0 the result is 0.

I could use this last solution but must be very careful not to fill in 0 in any fields as it turns my result into 0.

The perfect thing would be to ignore 0 and empty fields, and to have 0 as result even if all fields are empty.

Anyways, if there isn't a solutio for that, the formula daniele gave me will work just fine i think "Evaluate ( Substitute ( List ( larghezza ; lunghezza ; altezza ) ; ¶ ; "*" ) )"

Thanks!

• ###### 6. Re: Calculate Lenght*Width*Height even if one of them is null
You could enclose my calculation in a test like
`if(width OR height OR length ;if( width ; width ; 1 ) * if( length ; length ; 1) * if( height ; height; 1 ))which would treat both empty input and 0 in the same way.Jens`
• ###### 7. Re: Calculate Lenght*Width*Height even if one of them is null

That is perfect! Thank you!

The only thing is that if there is no input, the result cell remains empty. Hope that will not behave bad with future use. Thank you again!

• ###### 8. Re: Calculate Lenght*Width*Height even if one of them is null

> The only thing is that if there is no input, the result cell remains empty

Uncheck the box: Do not evaluate if all referenced fields are empty and go on with this calc:

Case(
lunghezza or larghezza or altezza ;
If ( lunghezza ; lunghezza ; 1 ) * If ( larghezza ; larghezza ; 1 ) * If ( altezza ; altezza; 1 ) ;
0
)

• ###### 9. Re: Calculate Lenght*Width*Height even if one of them is null

Thank you raibaudy , this solution also works for me, but since I am a completely new Filemaker user (yesterday was the first time I opened a database software, wich for me is Filemaker :smileyhappy:) I don't know the difference betwen this one and the one I remarked as "solution".

I would like these fields to be cloned as I tab trough the cells, like in this foto ( "Dynamic" Fields on Flickr, opens in new window)