Multiplying Cells When One Is Blank

LoriStrongin

Hello, I'm hoping someone can help me figure this out. I want to multiply the first three columns, with the total showing in Total Number of Delivered Training Hours. However, that field keeps showing as 0 if one of the cells are blank.

I tried using this formula, but it still just returns as zero:

=IF(OR([Total Number of Training Days]@row = "", [Total Number of Training Days]@row = 0), 0, ([Duration of Class]@row * [Total Number of Learners]@row))

Any guidance would be VERY appreciated. Thank you!

Paul Newcome

Does this work for you?

=IF([Total Number of Training Days]@row <> "", [Total Number of Training Days]@row, 1) * IF([Duration of Class]@row <> "", [Duration of Class]@row, 1) * IF([Total Number of Learners]@row <> "", [Total Number of Learners]@row, 1)

Nathan Slatton

Not sure if this would work but this is the thing i use for blank cells. just put if [Column Name]@row blank then do nothing otherwise do the normal if statement.

=IF(OR([Total Number of Training Days]@row="",[Total Number of Training Days]@row ="",[Duration of Class]@row="",[Total Number of Learners]@row ="", "Blank Cell",IF(OR([Total Number of Training Days]@row = "", [Total Number of Training Days]@row = 0), 0, ([Duration of Class]@row * [Total Number of Learners]@row))

LoriStrongin

Thanks for the super rapid response, Nathan. Unfortunately, that's giving me the INCORRECT ARGUMENT error.

prime_nathaniel

@LoriStrongin can you help me understand, how is it possible there were learners and a class but no training days? Wouldn't it in fact be 1 day and thus you always have all 3?

if no learners then no class
if class is 0 minutes long then no class
if class happened on 0 days then no class

Wouldn't this be a more realistic representation of what happened on that day?

Anyways, if your use case requires that it be blank rather than 1.

=if(or(isblank([Total Number of Training Days]@row),[Total Number of Training Days]@row = ""), [Total Number of Learners]@row*[Duration of Class]@row, [Total Number of Learners]@row*[Duration of Class]@row*[Total Number of Training Days]@row)

Paul Newcome

Does this work for you?

=IF([Total Number of Training Days]@row <> "", [Total Number of Training Days]@row, 1) * IF([Duration of Class]@row <> "", [Duration of Class]@row, 1) * IF([Total Number of Learners]@row <> "", [Total Number of Learners]@row, 1)

LoriStrongin

@Paul Newcome, YES!!! Thank you so much, this formula worked!!!

@Prime_Nathaniel, sometimes our training is only an hour or less, and not a full day, so we wanted to account for that in the dashboard I'm building from this data.

Thank you everyone for helping me figure this one out!

Paul Newcome

Happy to help. 👍️