Conditional formula - language needed

IThe short version of this question is above the line......you can help 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 in row 7 (J7, N7, R7, V7, Z7, AD7, AH7, AL7, AP7, AT7) are zero I want to 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 SEVERAL conditional formulas in cells that will be summed to get my desired 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 do 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 and 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…unless there is a way to combine these formulas in one cell.

Getting the above info would be very helpful, but explaining why i need it (below the line) may provide an even better solution. However, if you don't want to read on - i will be happy extrapolating from the above 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 from 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 there is a zero value in one and only one of the ten columns, I want to average the remaining columns and add that average to the total. If two columns and only two have a zero value I want to average the remaining 8 columns and add that average TWICE to the total; if three columns have a zero value I want to average the remaining 7 columns and add that average THREE TIMES to the total, etc., for 4 columns having a zero value, then 5, 6, 7, 8, 9, columns. I realize that this may have to be accomplished with several conditional statements in cells that will then be summed.


3. Column AW for each row sums the 10 columns mentioned in #1. It will remain hidden and will be used in the conditional formulas to find the averages.

4. Each conditional formula will look for “zero” values in each of the ten columns and get an average of the columns that do NOT have a zero 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!!! Mary

Hi gamebird,

Here's a forumula that you can use:

=IF(SUM(COUNTIF(J7,0),COUNTIF(N7,0),COUNTIF(R7,0),COUNTIF(V7,0),COUNTIF(Z7,0),COUNTIF(AD7,0),COUNTIF(AH7,0),COUNTIF(AL7,0),COUNTIF(AP7,0),COUNTIF(AT7,0))=2,(AW7/8)*2,"No Calculation")

If two (and ONLY two) responses are zero, then it will calculate (AW7/8)*2,
if there are LESS or MORE than two responses with zero, then it will show "No
Calculation".

The forumula is made up of separate COUNTIF functions for each cell (e.g.,
=COUNTIF(J7,0) and so on). Each function will return either 0 or 1. If the
value in the Cell is 0 it will return 1. If it is anything other than 0 it
will return zero. Each of these return values are added up. If the SUM of
these return values equals 2 (and ONLY 2) then the calculation will occur. If
not the text "No Calculation" will occur.

Hope that helps.

Regards,
James

PS - You can't use a multiple range for the COUNTIF function so that's why
there are multiple functions.

"gamebird" wrote:

>
> IThe short version of this question is above the line......you can help
> 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 in
> row 7 (J7, N7, R7, V7, Z7, AD7, AH7, AL7, AP7, AT7) are zero I want to
> 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 SEVERAL
> conditional formulas in cells that will be summed to get my desired
> 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 do
> 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 and
> 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…unless
> there is a way to combine these formulas in one cell.
>
> Getting the above info would be very helpful, but explaining why i need
> it (below the line) may provide an even better solution. However, if you
> don't want to read on - i will be happy extrapolating from the above
> 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 from
> 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 there
> is a zero value in one and only one of the ten columns, I want to
> average the remaining columns and add that average to the total. If two
> columns and only two have a zero value I want to average the remaining 8
> columns and add that average TWICE to the total; if three columns have a
> zero value I want to average the remaining 7 columns and add that
> average THREE TIMES to the total, etc., for 4 columns having a zero
> value, then 5, 6, 7, 8, 9, columns. I realize that this may have to be
> accomplished with several conditional statements in cells that will then
> be summed.
>
>
> 3. Column AW for each row sums the 10 columns mentioned in #1. It will
> remain hidden and will be used in the conditional formulas to find the
> averages.
>
> 4. Each conditional formula will look for “zero” values in each of the
> ten columns and get an average of the columns that do NOT have a zero
> 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!!! Mary
>
>
> --
> gamebird
> ------------------------------------------------------------------------
> gamebird's Profile: http://www.excelforum.com/member.php...o&userid=27963
> View this thread: http://www.excelforum.com/showthread...hreadid=474659
>
>

Hi!

Try this:

=IF(SUMPRODUCT(--(MOD(COLUMN(J7:AT7),4)=2),--(ISNUMBER(J7:AT7)),--(J7:AT7=0))=2,AW7/8*2,"")

Biff

"gamebird" <gamebird.1wo76d_1128920705.7225@excelforum-nospam.com> wrote in
message news:gamebird.1wo76d_1128920705.7225@excelforum-nospam.com...
>
> IThe short version of this question is above the line......you can help
> 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 in
> row 7 (J7, N7, R7, V7, Z7, AD7, AH7, AL7, AP7, AT7) are zero I want to
> 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 SEVERAL
> conditional formulas in cells that will be summed to get my desired
> 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 do
> 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 and
> 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.unless
> there is a way to combine these formulas in one cell.
>
> Getting the above info would be very helpful, but explaining why i need
> it (below the line) may provide an even better solution. However, if you
> don't want to read on - i will be happy extrapolating from the above
> 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 from
> 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 there
> is a zero value in one and only one of the ten columns, I want to
> average the remaining columns and add that average to the total. If two
> columns and only two have a zero value I want to average the remaining 8
> columns and add that average TWICE to the total; if three columns have a
> zero value I want to average the remaining 7 columns and add that
> average THREE TIMES to the total, etc., for 4 columns having a zero
> value, then 5, 6, 7, 8, 9, columns. I realize that this may have to be
> accomplished with several conditional statements in cells that will then
> be summed.
>
>
> 3. Column AW for each row sums the 10 columns mentioned in #1. It will
> remain hidden and will be used in the conditional formulas to find the
> averages.
>
> 4. Each conditional formula will look for "zero" values in each of the
> ten columns and get an average of the columns that do NOT have a zero
> 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!!! Mary
>
>
> --
> gamebird
> ------------------------------------------------------------------------
> gamebird's Profile:
> http://www.excelforum.com/member.php...o&userid=27963
> View this thread: http://www.excelforum.com/showthread...hreadid=474659
>

from this point, i will call your cells (J7, N7, R7, V7, Z7, AD7, AH7, AL7,
AP7, AT7) as "myData".

this formula reads :
sum + zeros found * sum / total data which is not zero

in your case :
sum + zeros found * sum / (10 - zeros found)

=SUM(myData)+COUNTIF(myData,0)*(SUM(myData)/COUNTIF(myData,"<>0")))



"gamebird" wrote:

>
> IThe short version of this question is above the line......you can help
> 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 in
> row 7 (J7, N7, R7, V7, Z7, AD7, AH7, AL7, AP7, AT7) are zero I want to
> 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 SEVERAL
> conditional formulas in cells that will be summed to get my desired
> 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 do
> 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 and
> 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…unless
> there is a way to combine these formulas in one cell.
>
> Getting the above info would be very helpful, but explaining why i need
> it (below the line) may provide an even better solution. However, if you
> don't want to read on - i will be happy extrapolating from the above
> 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 from
> 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 there
> is a zero value in one and only one of the ten columns, I want to
> average the remaining columns and add that average to the total. If two
> columns and only two have a zero value I want to average the remaining 8
> columns and add that average TWICE to the total; if three columns have a
> zero value I want to average the remaining 7 columns and add that
> average THREE TIMES to the total, etc., for 4 columns having a zero
> value, then 5, 6, 7, 8, 9, columns. I realize that this may have to be
> accomplished with several conditional statements in cells that will then
> be summed.
>
>
> 3. Column AW for each row sums the 10 columns mentioned in #1. It will
> remain hidden and will be used in the conditional formulas to find the
> averages.
>
> 4. Each conditional formula will look for “zero” values in each of the
> ten columns and get an average of the columns that do NOT have a zero
> 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!!! Mary
>
>
> --
> gamebird
> ------------------------------------------------------------------------
> gamebird's Profile: http://www.excelforum.com/member.php...o&userid=27963
> View this thread: http://www.excelforum.com/showthread...hreadid=474659
>
>

HI James - thank you profusely for not only your answer, but your explanation!!! Best, Mary

Originally Posted by JS2004R6

Hi gamebird,

Here's a forumula that you can use:

=IF(SUM(COUNTIF(J7,0),COUNTIF(N7,0),COUNTIF(R7,0),COUNTIF(V7,0),COUNTIF(Z7,0),COUNTIF(AD7,0),COUNTIF(AH7,0),COUNTIF(AL7,0),COUNTIF(AP7,0),COUNTIF(AT7,0))=2,(AW7/8)*2,"No Calculation")

If two (and ONLY two) responses are zero, then it will calculate (AW7/8)*2,
if there are LESS or MORE than two responses with zero, then it will show "No
Calculation".

The forumula is made up of separate COUNTIF functions for each cell (e.g.,
=COUNTIF(J7,0) and so on). Each function will return either 0 or 1. If the
value in the Cell is 0 it will return 1. If it is anything other than 0 it
will return zero. Each of these return values are added up. If the SUM of
these return values equals 2 (and ONLY 2) then the calculation will occur. If
not the text "No Calculation" will occur.

Hope that helps.

Regards,
James

PS - You can't use a multiple range for the COUNTIF function so that's why
there are multiple functions.

"gamebird" wrote:

>
> IThe short version of this question is above the line......you can help
> 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 in
> row 7 (J7, N7, R7, V7, Z7, AD7, AH7, AL7, AP7, AT7) are zero I want to
> 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 SEVERAL
> conditional formulas in cells that will be summed to get my desired
> 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 do
> 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 and
> 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…unless
> there is a way to combine these formulas in one cell.
>
> Getting the above info would be very helpful, but explaining why i need
> it (below the line) may provide an even better solution. However, if you
> don't want to read on - i will be happy extrapolating from the above
> 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 from
> 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 there
> is a zero value in one and only one of the ten columns, I want to
> average the remaining columns and add that average to the total. If two
> columns and only two have a zero value I want to average the remaining 8
> columns and add that average TWICE to the total; if three columns have a
> zero value I want to average the remaining 7 columns and add that
> average THREE TIMES to the total, etc., for 4 columns having a zero
> value, then 5, 6, 7, 8, 9, columns. I realize that this may have to be
> accomplished with several conditional statements in cells that will then
> be summed.
>
>
> 3. Column AW for each row sums the 10 columns mentioned in #1. It will
> remain hidden and will be used in the conditional formulas to find the
> averages.
>
> 4. Each conditional formula will look for “zero” values in each of the
> ten columns and get an average of the columns that do NOT have a zero
> 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!!! Mary
>
>
> --
> gamebird
> ------------------------------------------------------------------------
> gamebird's Profile: http://www.excelforum.com/member.php...o&userid=27963
> View this thread: http://www.excelforum.com/showthread...hreadid=474659
>
>

Thank you Alvin!! Best, Mary

Originally Posted by Alvin

from this point, i will call your cells (J7, N7, R7, V7, Z7, AD7, AH7, AL7,
AP7, AT7) as "myData".

this formula reads :
sum + zeros found * sum / total data which is not zero

in your case :
sum + zeros found * sum / (10 - zeros found)

=SUM(myData)+COUNTIF(myData,0)*(SUM(myData)/COUNTIF(myData,"<>0")))



"gamebird" wrote:

>
> IThe short version of this question is above the line......you can help
> 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 in
> row 7 (J7, N7, R7, V7, Z7, AD7, AH7, AL7, AP7, AT7) are zero I want to
> 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 SEVERAL
> conditional formulas in cells that will be summed to get my desired
> 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 do
> 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 and
> 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…unless
> there is a way to combine these formulas in one cell.
>
> Getting the above info would be very helpful, but explaining why i need
> it (below the line) may provide an even better solution. However, if you
> don't want to read on - i will be happy extrapolating from the above
> 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 from
> 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 there
> is a zero value in one and only one of the ten columns, I want to
> average the remaining columns and add that average to the total. If two
> columns and only two have a zero value I want to average the remaining 8
> columns and add that average TWICE to the total; if three columns have a
> zero value I want to average the remaining 7 columns and add that
> average THREE TIMES to the total, etc., for 4 columns having a zero
> value, then 5, 6, 7, 8, 9, columns. I realize that this may have to be
> accomplished with several conditional statements in cells that will then
> be summed.
>
>
> 3. Column AW for each row sums the 10 columns mentioned in #1. It will
> remain hidden and will be used in the conditional formulas to find the
> averages.
>
> 4. Each conditional formula will look for “zero” values in each of the
> ten columns and get an average of the columns that do NOT have a zero
> 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!!! Mary
>
>
> --
> gamebird
> ------------------------------------------------------------------------
> gamebird's Profile: http://www.excelforum.com/member.php...o&userid=27963
> View this thread: http://www.excelforum.com/showthread...hreadid=474659
>
>

Thanks, Biff!! Best, Mary

Originally Posted by Biff

Hi!

Try this:

=IF(SUMPRODUCT(--(MOD(COLUMN(J7:AT7),4)=2),--(ISNUMBER(J7:AT7)),--(J7:AT7=0))=2,AW7/8*2,"")

Biff

"gamebird" <gamebird.1wo76d_1128920705.7225@excelforum-nospam.com> wrote in
message news:gamebird.1wo76d_1128920705.7225@excelforum-nospam.com...
>
> IThe short version of this question is above the line......you can help
> 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 in
> row 7 (J7, N7, R7, V7, Z7, AD7, AH7, AL7, AP7, AT7) are zero I want to
> 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 SEVERAL
> conditional formulas in cells that will be summed to get my desired
> 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 do
> 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 and
> 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.unless
> there is a way to combine these formulas in one cell.
>
> Getting the above info would be very helpful, but explaining why i need
> it (below the line) may provide an even better solution. However, if you
> don't want to read on - i will be happy extrapolating from the above
> 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 from
> 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 there
> is a zero value in one and only one of the ten columns, I want to
> average the remaining columns and add that average to the total. If two
> columns and only two have a zero value I want to average the remaining 8
> columns and add that average TWICE to the total; if three columns have a
> zero value I want to average the remaining 7 columns and add that
> average THREE TIMES to the total, etc., for 4 columns having a zero
> value, then 5, 6, 7, 8, 9, columns. I realize that this may have to be
> accomplished with several conditional statements in cells that will then
> be summed.
>
>
> 3. Column AW for each row sums the 10 columns mentioned in #1. It will
> remain hidden and will be used in the conditional formulas to find the
> averages.
>
> 4. Each conditional formula will look for "zero" values in each of the
> ten columns and get an average of the columns that do NOT have a zero
> 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!!! Mary
>
>
> --
> gamebird
> ------------------------------------------------------------------------
> gamebird's Profile:
> http://www.excelforum.com/member.php...o&userid=27963
> View this thread: http://www.excelforum.com/showthread...hreadid=474659
>

Looks like, attempting to put all in a single formula:

=AVERAGE(IF((MOD(COLUMN($J$7:$AT$7)-COLUMN($J$7)+0,7)=0)*ISNUMBER($J$7:$AT$2),$J$7:$AT$7*MAX(1,SUM(($J$7:$AT$7=0)+0))))

which must be confirmed with control+shift+enter.
gamebird wrote:
> IThe short version of this question is above the line......you can help
> 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 in
> row 7 (J7, N7, R7, V7, Z7, AD7, AH7, AL7, AP7, AT7) are zero I want to
> 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 SEVERAL
> conditional formulas in cells that will be summed to get my desired
> 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 do
> 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 and
> 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…unless
> there is a way to combine these formulas in one cell.
>
> Getting the above info would be very helpful, but explaining why i need
> it (below the line) may provide an even better solution. However, if you
> don't want to read on - i will be happy extrapolating from the above
> 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 from
> 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 there
> is a zero value in one and only one of the ten columns, I want to
> average the remaining columns and add that average to the total. If two
> columns and only two have a zero value I want to average the remaining 8
> columns and add that average TWICE to the total; if three columns have a
> zero value I want to average the remaining 7 columns and add that
> average THREE TIMES to the total, etc., for 4 columns having a zero
> value, then 5, 6, 7, 8, 9, columns. I realize that this may have to be
> accomplished with several conditional statements in cells that will then
> be summed.
>
>
> 3. Column AW for each row sums the 10 columns mentioned in #1. It will
> remain hidden and will be used in the conditional formulas to find the
> averages.
>
> 4. Each conditional formula will look for “zero” values in each of the
> ten columns and get an average of the columns that do NOT have a zero
> 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!!! Mary
>
>

--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.