G
gamebird
IThe short version of this question is above the line......you can hel
a lot if you respond to only THAT.
I am using Excel 2003 on an XP machine.
If two - -and only two - - of the values in each of these 10 cells i
row 7 (J7, N7, R7, V7, Z7, AD7, AH7, AL7, AP7, AT7) are zero I want t
take AW7 and divide it by 8 and multiply that difference by 2.
Here’s how I see doing it - thinking that i may have to have SEVERA
conditional formulas in cells that will be summed to get my desire
result....
In one cell:
If j7=0 and n7 = 0 and r7 > 0 and v7 > 0 and z7 > 0 and ad7 > 0 and
ah7 > 0 and al7 > 0 and ap7 > 0 and at7 > 0, then (AW7/8)*2. (how d
you translate that into an Excel formula?)
in another cell
If j7=0 and n7 > 0 and r7 = 0 and v7 > 0 and z7 > 0 and ad7 > 0 an
ah7 > 0 and al7 > 0 and ap7 > 0 and at7 > 0, then (AW7/8)*2.
Etc…. with every combination of the two cells that could be zero…unles
there is a way to combine these formulas in one cell.
Getting the above info would be very helpful, but explaining why i nee
it (below the line) may provide an even better solution. However, if yo
don't want to read on - i will be happy extrapolating from the abov
info for a workable solution.
____________________
I ask this question because:
1. I am entering values in each of ten columns (J, N, R, V, Z, AD, AH
AL, AP, AT) and adding them together - along with summed values fro
cells with conditional formulas - in a “total” column. (E)
2. If there is a zero value in any of the ten columns (J, N, R, V, Z
AD, AH, AL, AP, AT,) the conditional values come into play. If ther
is a zero value in one and only one of the ten columns, I want t
average the remaining columns and add that average to the total. If tw
columns and only two have a zero value I want to average the remaining
columns and add that average TWICE to the total; if three columns have
zero value I want to average the remaining 7 columns and add tha
average THREE TIMES to the total, etc., for 4 columns having a zer
value, then 5, 6, 7, 8, 9, columns. I realize that this may have to b
accomplished with several conditional statements in cells that will the
be summed.
3. Column AW for each row sums the 10 columns mentioned in #1. It wil
remain hidden and will be used in the conditional formulas to find th
averages.
4. Each conditional formula will look for “zero” values in each of th
ten columns and get an average of the columns that do NOT have a zer
value. It will also multiply that average by 2 if there are 2 “zero
columns, 3 if there are 3 “zero” columns, 4,5,6, etc.
Thanks so much!!! Mar
a lot if you respond to only THAT.
I am using Excel 2003 on an XP machine.
If two - -and only two - - of the values in each of these 10 cells i
row 7 (J7, N7, R7, V7, Z7, AD7, AH7, AL7, AP7, AT7) are zero I want t
take AW7 and divide it by 8 and multiply that difference by 2.
Here’s how I see doing it - thinking that i may have to have SEVERA
conditional formulas in cells that will be summed to get my desire
result....
In one cell:
If j7=0 and n7 = 0 and r7 > 0 and v7 > 0 and z7 > 0 and ad7 > 0 and
ah7 > 0 and al7 > 0 and ap7 > 0 and at7 > 0, then (AW7/8)*2. (how d
you translate that into an Excel formula?)
in another cell
If j7=0 and n7 > 0 and r7 = 0 and v7 > 0 and z7 > 0 and ad7 > 0 an
ah7 > 0 and al7 > 0 and ap7 > 0 and at7 > 0, then (AW7/8)*2.
Etc…. with every combination of the two cells that could be zero…unles
there is a way to combine these formulas in one cell.
Getting the above info would be very helpful, but explaining why i nee
it (below the line) may provide an even better solution. However, if yo
don't want to read on - i will be happy extrapolating from the abov
info for a workable solution.
____________________
I ask this question because:
1. I am entering values in each of ten columns (J, N, R, V, Z, AD, AH
AL, AP, AT) and adding them together - along with summed values fro
cells with conditional formulas - in a “total” column. (E)
2. If there is a zero value in any of the ten columns (J, N, R, V, Z
AD, AH, AL, AP, AT,) the conditional values come into play. If ther
is a zero value in one and only one of the ten columns, I want t
average the remaining columns and add that average to the total. If tw
columns and only two have a zero value I want to average the remaining
columns and add that average TWICE to the total; if three columns have
zero value I want to average the remaining 7 columns and add tha
average THREE TIMES to the total, etc., for 4 columns having a zer
value, then 5, 6, 7, 8, 9, columns. I realize that this may have to b
accomplished with several conditional statements in cells that will the
be summed.
3. Column AW for each row sums the 10 columns mentioned in #1. It wil
remain hidden and will be used in the conditional formulas to find th
averages.
4. Each conditional formula will look for “zero” values in each of th
ten columns and get an average of the columns that do NOT have a zer
value. It will also multiply that average by 2 if there are 2 “zero
columns, 3 if there are 3 “zero” columns, 4,5,6, etc.
Thanks so much!!! Mar