If field is blank divide by a different cell

[BigD1980]

Hi all, new poster here. Please be gentle with me.

I will regularly be producing tables as per the below. The data is a product code, batch number, batch volume, whether the product pH was adjusted using NaOH or HCl and the amount (ml) added per batch. I then need it to calculate how much was added per Litre of either NaOH or HCl. For example this would be D2/C2 in row 2, however in row 3 it would need to be E4/C4. I have tried playing aroud with IF and IFERROR. It seems like it should be a really simple formula, but I have been unsuccessful so far.

Apologies in advance for my lack of knowledge and thanks in advance for any help.

 

[Special-K99]

The data you've supplied implies there will ALWAYS be a value in D or E.
Based on that

=MAX(D2:E2)/C2

should work

 

[BigD1980]

Hi all, new poster here. Please be gentle with me.

I will regularly be producing tables as per the below. The data is a product code, batch number, batch volume, whether the product pH was adjusted using NaOH or HCl and the amount (ml) added per batch. I then need it to calculate how much was added per Litre of either NaOH or HCl. For example this would be D2/C2 in row 2, however in row 3 it would need to be E4/C4. I have tried playing aroud with IF and IFERROR. It seems like it should be a really simple formula, but I have been unsuccessful so far.

View attachment 108772

Apologies in advance for my lack of knowledge and thanks in advance for any help.

Correction - row 3 would be E4/C4

 

[BigD1980]

The data you've supplied implies there will ALWAYS be a value in D or E.
Based on that

=MAX(D2:E2)/C2

should work

Perfect! Thank you so much, I was attacking it fromthe wrong end.

 

[Special-K99]

Ah yep. ignore this reply. I misread your reply.
The formula should work.

 

[bobsan42]

there are many ways to resolve this. Here is one of them;Formula in F2 (and down):

=SUM(D2:E2)/C2

 

[bobsan42]

Perfect! Thank you so much, I was attacking it fromthe wrong end.

Not necessarily, here is an if solution:

=IF(ISBLANK(D2),E2,D2)/C2

or you can wrap it in IFERROR to avoid errors when C2 is empty:

=IFERROR(IF(ISBLANK(D2),E2,D2)/C2,"")

 

[BigD1980]

I should have made it clear that occasionally there will be no data in column D or E. Using the =SUM(D2:E2)/C2 formula therefore works better. Thank you both for such a quick response, it is very much appreciated. Have a good day.

​

 

[BigD1980]

Not necessarily, here is an if solution:

=IF(ISBLANK(D2),E2,D2)/C2

or you can wrap it in IFERROR to avoid errors when C2 is empty:

=IFERROR(IF(ISBLANK(D2),E2,D2)/C2,"")

Wow! There really is more than one way to skin a cat, as long as you are clever enough!