Formular that can handle null values in multiple cell

I have two columns that I need to calculate the Conversion rate, however sometimes the cell value are filled either both or only for one. Need help to create a form that ONLY calculates if both cells (marked in yellow) has values
I have tried to find the solution from previous similar questions, but I cant get it to work




IF(AND({Registered}<>"",{No Show}<>"",{No Show}<>0),DIVIDE({Registered},{No Show}),"N/A")

The conditions are {Registered} not empty, {No Show} not empty and also not 0. If these conditions are met, divide {Registered} by {No show}. If not met, then N/A (you can remove it if you don’t want N/A or change it for something else).

Great input - thanks :slight_smile:

For the cells with no data I get the red exclamation mark (see image). Can the formula be set to not calculate, if there is no data and just deliver a blank?

Bonus question: How do I “add” the % sign to the value?

To get a blank result, replace “N/A” by “”.
As to the error, please include your formula because it shouldn’t show an error.

To add a %, use CONCATENATE => CONCATENATE(DIVIDE({Registered},{No Show}),“%”)
You’ll probably end up with many decimals. To limit the number of decimals, use ROUND.
Replace 2 with the number of decimals you want.

CONCATENATE(ROUND(DIVIDE({Registered},{No Show}),2),"%")
1 Like

Should the Concatenate statement appear together with the org statement? The formular fails in this setup :