• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 190
  • Last Modified:

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

screen capture for idea about issueje-ee-missingvalues.xlsx
0
justearth
Asked:
justearth
  • 3
  • 2
  • 2
  • +1
1 Solution
 
StephenJRCommented:
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
 
Saqib Husain, SyedEngineerCommented:
=IF(COUNT(B2:F2)<5,-99999,SLOPE(B2:F2,B$1:F$1))

=IF(COUNT(B2:F2)<2,-99999,(SUM(B2:F2)/5))
0
 
justearthAuthor Commented:
Thanks, awesome! I didn't know about COUNTBLANK.

Cheers,
JE
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
justearthAuthor Commented:
Thanks again.
0
 
Saqib Husain, SyedEngineerCommented:
Sorry the equation for mean should be

=IF(COUNT(B2:F2)<3,-99999,(SUM(B2:F2)/5))
0
 
justearthAuthor Commented:
Thanks for the follow up ssaqibh.

Cheers,
JE
0
 
TonyWongCommented:

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
 
TonyWongCommented:
Damn my incredibly slooooooooow hands.......
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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