Hello,
i had a previous thread ....which was solved ....
http://www.excelforum.com/excel-prog...e-formula.html
The issue I am having is ...there are certain rows in my WB tht will have a "0.00" value ... when I use this formula:
I get a DIV/0 error ... how can a resolve this ...
=AVERAGE(IF((D7:DC7= "Planned")*(D10:DC10),D10:DC10))
I have tried the following but it doesn't seem to work ...
Thank you in advance.....=IF(SUM(D7:DC7)<>0,AVERAGE(IF((D26:DC26="Planned")*(D7:DC7),D7:DC7)),0)
Last edited by arvin; 07-07-2010 at 02:09 PM. Reason: Issue has been solved
It works for me if all of D7:DC7 are =0 then you should get 0.
How is it not working... error or wrong value?
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Hi,
This will return the average of cell D10:DC10 if the relative cell in row 7 contains "Planned". It should include cells in row 10 that contain zero.
=AVERAGE(IF(D7:DC7= "Planned",D10:DC10,""))
entered using CTRL, SHIFT and ENTER
Last edited by sweep; 07-07-2010 at 10:03 AM.
Rule 1: Never merge cells
Rule 2: See rule 1
"Tomorrow I'm going to be famous. All I need is a tennis racket and a hat".
Try
=IF(SUM((D26:DC26="Planned")*(D7:DC7))<>0,AVERAGE(IF((D26:DC26="Planned")*(D7:DC7),D7:DC7)),0)
Audere est facere
This gave me a "#VALUE" error....Originally Posted by daddylonglegs
I would like to post an example file but I keep on getting a DB error on the site
Database error
The Excel Help Forum database has encountered a problem.
--------------------------------------------------------------------------------
Please try the following:
Load the page again by clicking the Refresh button in your web browser.
Open the www.excelforum.com home page, then try to open another page.
Click the Back button to try another link.
The www.excelforum.com forum technical staff have been notified of the error, though you may contact them if the problem persists.
We apologise for any inconvenience.
Is it too big? Try zipping the file first.
Strange ... its not over the limit but zipping the file worked ...thank you
Opps ...sorry but I forgot to mention that .....
I'm trying to include this formula on the "KPI Analysis" Tab under Columns "DD" and DE" "YTD Averages"
I tried Daddylonglegs' formula and it worked
Did you remember to confirm it with CTRL+SHIFT+ENTER?
Hello,
Yes I did .... and I still got the #Value!
Did you try that formula in the WB that I posted?
The formula needs to absolutely reference row 7:
=IF(SUM(($D$7:$DC$7="Planned")*(D10:DC10))<>0,AVERAGE(IF(($D$7:$DC$7="Planned")*(D10:DC10),D10:DC10)),0)
copied down after confirming with CSE
Yup! .... that did it...thank you so much everyone!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
arvin 
Register To Reply
Bookmarks