How to calculate only one of two ranged cells both with formulas that reference each other

[agrayray]

Before I used ranges, it had a lot of paired cells that uses formulas (with COUNT() ) to convert one OR the other. This allowed ppl to enter either one of the cells and the formulas would calculate the other.

For example, if you have a series of weights for a series of dates
A-DATE, B-KG, C-LBS:

A		B	C
1 DATE	KG	LBS
2 04/22/20	71.1	156.8
3 04/23/20	70.4	155.2
4 04/24/20	70.4	155.2

KG, B2 formula’s would be:
=IF(COUNT(C2), C2/2.205, “”)

and LBS, C2 would be:
=IF(COUNT(B2), B2*2.205, “”)

etc…

I now have switched to using ranges for KG and LBS (columns B and C respectively) and
no longer can find a way to do this. The count function is not valid as it counts the all the range whereas I really just want the formula to apply to a given row for the date in this case.

How can I simply test to see if one column with a formula has a value or not for a cell that is part of a range?

islbank() doesnt work because all the cells have formulas.

IF(KG="", IF(KG<>"", IF(ISNUMBER(KG) etc… and several other alternatives dont seem to work either…

IF(B2="")… formula for C2 only works if no formula is in B2, because if you also enter formula IF(C2="") in B2, you get Err:522 cell reference itself errors:

  B			C
2 =IF(C2=""...)	=IF(B2=""..)

Can anyone please help me out?

THANKS IN ADVANCE!!!

[anon73440385]

This won’t work. You seem to neglect the fact, that you delete the formula in one of your cells when you enter a value (your original case). A cell can only have either a formula or a value - not both. This works as long as you do that row by row. Now you want to work with ranges (named ranges - but in fact naming doesn’t play a role) and the same principle as for cells applies for the whole range - either the range has value or an array formula, but not both. On the other side, you still want to enter a value into single cell, which would require part of an array being a formula and part being values as per your (dynamic) choice (Array Functions)

> ... that reference each other

Referencing each other by definition is a circular reference. Therefore even your initial use case only works due to use COUNT(), which luckily yields 0, if the content of the referenced cell is a (non-evaluated) formula and the circular reference gets eliminated on input in one cel