# Can this be modified?

This is a discussion on Can this be modified? within the Excel Questions forums, part of the Question Forums category; I have this formula (thanks to NateO!): {=SUM(--(Data!\$D12:\$G12=Data!\$D\$20:\$G\$20))/COUNTA(Data!\$D12:\$G12)} I would like to change it a bit though.... Could I modify ...

1. ## Can this be modified?

I have this formula (thanks to NateO!):

{=SUM(--(Data!\$D12:\$G12=Data!\$D\$20:\$G\$20))/COUNTA(Data!\$D12:\$G12)}

I would like to change it a bit though.... Could I modify it to contain an IF stmt that would return a #N/A if referenced cells in D12:G12 contain no data?

Help is always most greatly appreciated!!

Cheers
S.

2. ## Re: Can this be modified?

Originally Posted by simfonik
I have this formula (thanks to NateO!):

{=SUM(--(Data!\$D12:\$G12=Data!\$D\$20:\$G\$20))/COUNTA(Data!\$D12:\$G12)}

I would like to change it a bit though.... Could I modify it to contain an IF stmt that would return a #N/A if referenced cells in D12:G12 contain no data?

Help is always most greatly appreciated!!

Cheers
S.
=IF(COUNTA(Data!\$D12:\$G12),SUMPRODUCT(--(Data!\$D12:\$G12=Data!\$D\$20:\$G\$20))/COUNTA(Data!\$D12:\$G12),#N/A)

which doesn't need control+shift+enter.

3. Nice one, Aladin! Thanks for the help mate.

4. ## Just noticed something

I just noticed something in this formula, and was wondering if someone might be able to provide a solution:

=IF(COUNTA(Data!\$D137:\$G137),SUMPRODUCT(--(Data!\$D137:\$G137=Data!\$D\$151:\$G\$151))/COUNTA(Data!\$D137:\$G137),#N/A)

If any of the cells in the specified range are blank, then it does not return the correct/desired value.

exmple of what the above formula is referencing:

******** ******************** ************************************************************************>
 Microsoft Excel - Client Services Support Call Listening Package2.xls ___Running: xl2000 : OS = Windows Windows 2000
 (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
 D137 =

D
E
F
G
137
NeutralGoodGood*
138
NeutralGoodGood*
139
**Good*
140
NeutralGoodGood*
141
****
142
NeutralGoodNeutral*
143
Neutral***
144
Neutral***
145
****
146
**Good*
147
NeutralGoodGood*
148
****
149
NeutralGoodGood*
150
NeutralGoodGood*
151
NeutralGoodGood*
 Data *

[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

Since there is a 3 for 3 match in this case, the value should return (or at least is desired to) as 100%, but comes back as 133%.

Is it possible to nest a countif within the formula or something else, so the formula will still work when the entire range is not used?

THANKS!!

5. ## Re: Just noticed something

Originally Posted by simfonik
I just noticed something in this formula, and was wondering if someone might be able to provide a solution:

=IF(COUNTA(Data!\$D137:\$G137),SUMPRODUCT(--(Data!\$D137:\$G137=Data!\$D\$151:\$G\$151))/COUNTA(Data!\$D137:\$G137),#N/A)

If any of the cells in the specified range are blank, then it does not return the correct/desired value.

exmple of what the above formula is referencing:

...

Since there is a 3 for 3 match in this case, the value should return (or at least is desired to) as 100%, but comes back as 133%.

Is it possible to nest a countif within the formula or something else, so the formula will still work when the entire range is not used?

THANKS!!
It's rather your data that creates trouble.
If gaps can occur anywhere and you have to compare 4 cells at most, a cheaper and more robust formula would be waht we would want to have.

******** ******************** ************************************************************************>
 Microsoft Excel - Book7 ___Running: xl2000 : OS = Windows Windows 2000
 (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
 H137H138H139H140H141H142H143H144H145H146H147H148H149H150 =

D
E
F
G
H
136
*****
137
NeutralGoodGood*1
138
NeutralGoodGood*1
139
**Good*1
140
NeutralGoodGood*1
141
****#N/A
142
NeutralGoodNeutral*0.666667
143
Neutral***1
144
Neutral***1
145
****#N/A
146
**Good*1
147
NeutralGoodGood*1
148
****#N/A
149
NeutralGoodGood*1
150
NeutralGoodGood*1
151
NeutralGoodGood**
 Data *

[HtmlMaker 2.32] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

The formula in H2 is:

=IF(COUNTA(Data!D137:G137),(AND(Data!D137<>"",Data!D137=Data!\$D\$151)+AND(Data!E137<>"",Data!E137=Data!\$E\$151)+AND(Data!F137<>"",Data!F137=Data!\$F\$151)+AND(Data!G137<>"",Data!G137=Data!\$G\$151))/COUNTA(Data!D137:G137),#N/A)

6. Beautiful! A much better idea. Thank you again, Aladin!

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•