Please see the attached workbook.
In cell S4 I am calculating the index for all 3 categories. (The way I do this is not the
issue/question in this case).
The source tabel (to the left) is originally a pivot table.
Sometimes the one or two of the ILY-cells are blanc and then I am facing a problem
in the calculation in cell S4. (#DIV/0!)
How can I come around this problem ?
Last edited by Saturn; 03-10-2012 at 06:07 AM.
Hi Saturn,
I would suggest you to do little research on below options:-
One is two use GetPivotData function....
Another is two use formula like... =IF(isblank(e4),"",e4)...
If you face any issues, post the sample file with pivot. Thanks.
Regards,
DILIPandey
<click on below 'star' if this helps>
DILIPandey, Excel rMVP
+919810929744 (India), +971528225509 (Dubai), dilipandey@gmail.com
Thanks. I have tried the ISBLANK and variances of 'IF/OR/AND' but I am stuck.
Any others who have suggestions for solving this problem?
Hi
You can try this.
=IF(OR(E4="";I4="");"";(D4+F4+H4) / (D4/(E4/100)+F4/(G4/100)+H4/(I4/100)) * 100)
Hope to helps you.
Regards
Fotis.
-This is my Greek whisper to Europe.
--Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.
Advanced Excel Techniques: http://excelxor.com/
--KISS(Keep it simple Stupid)
--Bring them back.
---See about Acropolis of Athens.
--Visit Greece.
Hi Saturn,
I would like to give a try to GetPivot, please upload a sample file. Thanks.
Regards,
DILIPandey
<click on below 'star' if this helps>
Hi Fotis191 and dilipandey
Thanks a lot to both of you.
The suggestion to dilipandy doesn't work as I have tried to blank out E4, G4 and I4 in the attached file.
So: one or two of either E4, G4 and I4 might be blank, and then I want that (blank) cell to be ignored when the calculation is done.
I can't produce a sample with the pivot table. Pivot table or not, the calculation is done outside the PT.
Saturn
Hi Saturn,
GetPivot requires Pivot table, hence I asked for the same. If you are unable to produce the pivot, then I would suggest you to go with the solution provided by Fotis and enhance / upgrade the same. Thanks.
Regards,
DILIPandey
<click on below 'star' if this helps>
This works for you?
=IF(OR(E4="";I4="";G4="");"";(D4+F4+H4) / (D4/(E4/100)+F4/(G4/100)+H4/(I4/100)) * 100)
Change semi-colons to gomma, if you have to do it.
I think this formula has the same result:
=IF(E4*G4*I4=0,"",(D4+F4+H4) / (D4/E4+F4/G4+H4/I4))
I wonder why we have to devide all by 100 then multiply by 100?
Oldman Chatting: thanhmy_pham@yahoo.com Mailing: thanhmypham@gmail.com
According to this calc. the result is blank if E4 and/or G4 and/or I4 is blank. Previously it become #DIV/0!
I have to multiply and divid with 100 to have the result be shown as an index. (100 is same result as last year, 200 = double, 50 = half...)
Well, it is acording to you to "show as an index", but does my formula work?
Hi ptm0412
No, unfortunally it doesn't. One or more blank cells (E/G/I) results in a 'blank' result.
Saturn
@ptm0412
I angree with you, but i don't care! If OP wants this,then OK....!...I wonder why we have to devide all by 100 then multiply by 100?
@Saturn
The formula that i offered you, does,all these that you are looking for...!
So, i think that you are looking for something else....but you do not explain to us...what is this.....!
Dear Fotis,
That question I ask Saturn and he replied already. I am sorry if you misunderstand that I asked you.
Dear Saturn,
May your need is:
IF(E4*G4*I4=0,"",(D4*(E4>0)+F4*(G4>0)+H4*(I4>0))/(IF(E4>0,D4/E4,0)+IF(G4>0,F4/G4,0)+IF(I4>0,H4/I4,0)))
It means the formula ignore blank cells within E4, G4, I4, and calculate the rests.
(Divide by and multiply with what you want)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Saturn 
Register To Reply
Bookmarks