• Status: Solved
• Priority: Medium
• Security: Public
• Views: 192

# Change cell values based on missing data presecence.

Hello,

I have a worksheet with 6 groups, I have included an excerpt with 2.  The data has years 1 - 10.

*slope92 and mean92 use values from years 1 -5
*slope01 and mean01 use values from years 6 -10

The data has missing values indicated by blanks:
1.) I need the "slope" values to be "-9999" if there are any missing values (blanks) in the row.
2.) I need the "mean" values to be "-9999" if there are more than 2 missing values (blanks) in the row.

Can this be done with a macro or formula? Annotation appreciated.

Thank you kindly,
JE

je-ee-missingvalues.xlsx
0
justearth
• 3
• 2
• 2
• +1
1 Solution

Commented:
Try these

L2: =IF(COUNTBLANK(B2:F2)>0,-9999,SLOPE(B2:F2,B\$1:F\$1))
M2: =IF(COUNTBLANK(B2:F2)>2,-9999,SUM(B2:F2)/5)
0

EngineerCommented:
=IF(COUNT(B2:F2)<5,-99999,SLOPE(B2:F2,B\$1:F\$1))

=IF(COUNT(B2:F2)<2,-99999,(SUM(B2:F2)/5))
0

Author Commented:
Thanks, awesome! I didn't know about COUNTBLANK.

Cheers,
JE
0

Author Commented:
Thanks again.
0

EngineerCommented:
Sorry the equation for mean should be

=IF(COUNT(B2:F2)<3,-99999,(SUM(B2:F2)/5))
0

Author Commented:
Thanks for the follow up ssaqibh.

Cheers,
JE
0

Commented:

Hi there,

Amended your formulas slightly to accomodate the criteria you requested.

For Slope92:
=IF(OR(B2="",C2="",D2="",E2="",F2=""),"-9999",SLOPE(B2:F2,B\$1:F\$1))

The above states if there are ANY blanks in range then value equals "-9999".
If no blanks then SLOPE function.

For Mean92:
=IF(COUNTBLANK(B2:F2)>=2,"-9999",(SUM(B2:F2)/5))
This one states if there are more than 2 blanks in range then value equals "-9999".
If one blank or none at all then the SUM function.

Look at the attached amended workbook.

Cheers,
TOny

je-ee-missingvalues.xlsx
0

Commented:
Damn my incredibly slooooooooow hands.......
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

## Featured Post

• 3
• 2
• 2
• +1
Tackle projects and never again get stuck behind a technical roadblock.