Hi,
I am new here and just starting with excel. I have six empty cells, let say cells: A1,B1,C1,D1,E1 and F1. I need to make formula that calculates 1/A1+1/B1+1/C1+1/D1+1/E1+1/F1. Obviously it works fine when there are numbers in all cells but if only one cell is empty formula wont work. Could someone please assist. Tnx
What do you want to happen if one or more of the cells is blank.
If they all have to be present, you could just have something like:
=IFERROR( ... your formula ... , "data missing; please review")
Regards, TMS
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
Try this ARRAY FORMULA, completed by holding down CTRL and SHIFT when you press ENTER (instead of just pressing ENTER)
=SUM(IFERROR(1/A1:F1,0))
EDITED TO INCLUDE THIS:
I just thought of a way to achieve that sum using a regular formula:
=SUMPRODUCT((A1:F1<>"")/(A1:F1+(A1:F1="")))
Is that something you can work with?
Last edited by Ron Coderre; 07-21-2013 at 06:27 PM.
Yes, this formula works perfect. Could I just border you with few more questions. I presume IFERROR function when it gets to empty cells and tries to divide with them just returns value if error(in this case 0) but what is the difference between CRTL+SHIFT+ENTER and just ENTER. Once again many thanks.
Yes that second formula also works fine. Once again thank you.
While the explanation is complicated, it's easier to just say that some formulas only work when you use CTRL+SHIFT+ENTER.
Any easy way to spot those formulas is...
• Select the cell containing the formula that resolves to an error or an unexpected value
• Press F2...Press: F9
If the correct result displays, C+S+E will probably make the formula functional.
• Press ESC to restore the formula.
I'll need some time to figure out second formula as I am not sure what is multiplied by what. Tnx.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks