Forum Discussion
NOOB needs help please with formula
Good day all
I'm trying to perform a simple calculation to add together cell values, with each value as such (1/cell)+(1/cell) etc etc
Multiple cells in the table to be added together but I want a way to ignore a cell with no value, so say the range is A1-A9 and one of the cells doesn't contain a value it should be ignored in the calculation..
So far I'm here:
=1/(1/F8)+(1/H8)+(1/J8)+(1/L8)+(1/N8)+(1/P8)+(1/R8)+(1/T8)+(1/F10)+(1/H10)+(1/J10)+(1/L10)+(1/N10)+(1/P10)+(1/R10)+(1/T10)+(1/F12)+(1/H12)+(1/J12)+(1/L12)+(1/N12)+(1/P12)+(1/R12)+(1/T12)+(1/F14)+(1/H14)+(1/J14)+(1/L14)+(1/N14)+(1/P14)+(1/R14)+(1/T14)+(1/F16)+(1/H16)+(1/J16)+(1/L16)+(1/N16)+(1/P16)+(1/R16)+(1/T16)
But if one of the cells contains a 0 (zero) it produces #DIV/0!
This noob is grateful for your help
David (Inverness)
Use
=1/SUM(IF((F8:T16<>0)*ISEVEN(ROW(F8:T16))*ISEVEN(COLUMN(F8:T16)),1/F8:T16))
If you do not have Microsoft 365 or Office 2021, confirm the formula by pressing Ctrl+Shift+Enter.
- Most excellent, thank you very much 🙂
To avoid the #DIV/0! error when dividing by zero, you can use the IFERROR function along with an IF statement to check if a cell is zero before performing the calculation.
Here is how you can modify your formula:
=IFERROR(1/(1/F8), 0) + IFERROR(1/(1/H8), 0) + IFERROR(1/(1/J8), 0) + IFERROR(1/(1/L8), 0) + IFERROR(1/(1/N8), 0) + IFERROR(1/(1/P8), 0) + IFERROR(1/(1/R8), 0) + IFERROR(1/(1/T8), 0) + IFERROR(1/(1/F10), 0) + IFERROR(1/(1/H10), 0) + IFERROR(1/(1/J10), 0) + IFERROR(1/(1/L10), 0) + IFERROR(1/(1/N10), 0) + IFERROR(1/(1/P10), 0) + IFERROR(1/(1/R10), 0) + IFERROR(1/(1/T10), 0) + IFERROR(1/(1/F12), 0) + IFERROR(1/(1/H12), 0) + IFERROR(1/(1/J12), 0) + IFERROR(1/(1/L12), 0) + IFERROR(1/(1/N12), 0) + IFERROR(1/(1/P12), 0) + IFERROR(1/(1/R12), 0) + IFERROR(1/(1/T12), 0) + IFERROR(1/(1/F14), 0) + IFERROR(1/(1/H14), 0) + IFERROR(1/(1/J14), 0) + IFERROR(1/(1/L14), 0) + IFERROR(1/(1/N14), 0) + IFERROR(1/(1/P14), 0) + IFERROR(1/(1/R14), 0) + IFERROR(1/(1/T14), 0) + IFERROR(1/(1/F16), 0) + IFERROR(1/(1/H16), 0) + IFERROR(1/(1/J16), 0) + IFERROR(1/(1/L16), 0) + IFERROR(1/(1/N16), 0) + IFERROR(1/(1/P16), 0) + IFERROR(1/(1/R16), 0) + IFERROR(1/(1/T16), 0)
This formula will add the reciprocal of each cell value, but if any cell contains zero or is blank, it will ignore it and proceed with the calculation without causing the #DIV/0! error.
Another approach to achieve the same result is by using the SUMPRODUCT function along with an array formula. Here's how you can do it:
=SUMPRODUCT(1/(1/(F8:T16))*(F8:T16<>""))
This formula multiplies each cell by its reciprocal (1/cell) and then sums the products together. The (F8:T16<>"") part creates an array of TRUE/FALSE values where TRUE represents non-empty cells. Multiplying by this array effectively filters out the empty cells from the calculation. The text was revised with the AI.
My answers are voluntary and without guarantee!
Hope this will help you.
Was the answer useful? Mark as best response and like it!
This will help all forum participants.
- Thank you very much, it works most excellently 🙂
