Thursday, May 2, 2013

yogi_Compute Values In Specified Columns -- If Blank Enter NEED DATE And If 0 Enter N/A


                                          Google Spreadsheet   Post  #1166
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   May 02, 2013
user Kia Country of Charleston  (http://productforums.google.com/forum/?zx=crpyw0e3sdva#!mydiscussions/docs/_q7E5IKQux8)
Nested If & Checking Against Cells on Same Row
https://docs.google.com/spreadsheet/ccc?key=0AhNeV3ffLoi3dG9nVllvdi1ZQjFja3FsLXVQdi1SZWc&usp=sharing

I have setup the spreadsheet to track the life of a vehicle between the day it comes into the dealership and the steps before it gets posted online. So I basically have a list of dates across different departments going horizontally. 

For the Internet Department (highlighted header in light blue), there are times when the vehicle will be sold before they have a chance to take pictures, make descriptions, etc. I still want that vehicle's steps to be tracked between entry and sale even if it doesn't reach all departments. I told the Internet Department to enter in the exact same Date of Entry for all vehicles that they did not have a hand in. That way, the number of days between "Date of Entry" and each of the steps will always be 0. However, this is now throwing off my averages because they total of the respective columns should not be divided by all the number of rows. 

I noticed that tha AVERAGE function will only divide by the amount of cells that have a number filled in, so I figure that if I could make a rule that made applies text such as "SOLD BEFORE" or something like that, then the average at the bottom will be correct because the function will ignore the cells with text inside. The problem with that is that the cells in that column already have an IF function inside. 

I am way in over my head here. PLEASE HELP!
---
I want cells in columns P through T to look down their respective rows and display text if they all have a value of 0. For example, P2 thought T2 all have values of 0. Therefore, I want P2 to display "N/A". That way P71 which displays a running average and doesn't take into account the cells with "N/A" in them. 

I want this to apply to cells Q2, R2, S2, and T2 as well. So Q2, will check against P2, R2, S2, and T2 to decide whether to display a numerical value or "N/A"

I am not opposed to adding a column as sort of the middle man and having Q2, R2, S2, and T2 check against one cell vs. multiple cells. 

THANK YOU SO MUCH FOR YOUR HELP
---
As far as a cell being a "middle-man", it would be more like IF cells I2 through M2 have the same date as C2, then display "N/A". 

Then Q2 could say IF N2="N/A", then display "N/A", if NOT, then proceed with the IF function already in place.... 
--------------------------------------------------------------------------------------------------------------
following is a solution based on my best understanding of what you are trying to accomplish



cell A53 below

No comments:

Post a Comment