Modify Uptime formula in Excel VBA 2010

[DoodlesMama]

Excel 2010
Windows 7
​

Hello,
I'm at a beginner level in creating Excel formulas and using them in VBA and need to change the formula below to reflect a blank cell if there is no data to calculate rather than reflecting a 100%.



[SAT_UPTIME_METRICS1] = _
"=1-(SUMIFS(EFFT_RESOLVE_HRS2,EFFT_WEEKDAY2,7,EXCEPTION2,""<>YES"")/SATURDAY_HRS)"
[SAT_UPTIME_METRICS1] = [SAT_UPTIME_METRICS1].Value




[UPTIME_METRICS1] = _
"=1-(SUMIFS(EFFT_RESOLVE_HRS2,STATE2,STATES1_1,EFFT_WEEKDAY2,""<>7"",DEVICE2,UPTIME_DEV1,EXCEPTION2,""<>YES"")/WEEKDAY_HRS)"
[UPTIME_METRICS1] = [UPTIME_METRICS1].Value


See tables below for what it looks like currently and what I want it to look like.

BEFORE

SLA =	99%
SEVERITY =	ALL
DEVICE =	TAB
SATURDAY	56.67%	<---SATURDAY FORMULA
TOTAL	100%	<---NEED TO CREATE
AR	100%	<---REG FORMULA
AZ	100%
CA	100%
CT	100%
DC	100%
DE	100%
FL	100%
GA	100%
IA	100%
KS	100%

<TBODY> [TD="align: center"]UPTIME %[/TD]
[TD="align: center"][/TD]

</TBODY>

AFTER

SLA =	99%
SEVERITY =	ALL
DEVICE =	TAB
SATURDAY	56.67%	<---SATURDAY FORMULA
TOTAL	100%	<---NEED TO CREATE
AR	100%	<---REG FORMULA
AZ
CA
CT	100%
DC
DE	100%
FL
GA	100%
IA
KS	100%

<TBODY> [TD="align: center"]UPTIME %[/TD]
[TD="align: center"][/TD]

</TBODY>

 

[s hal]

The easiest way that I see without knowing what that formula is doing and not wanting to guess, is to create an if for the 0 condition that I assume is causing 100% (1-nothing = 100%), so I'm hazarding a guess at the following working:


[SAT_UPTIME_METRICS1] = _
"=if(SUMIFS(EFFT_RESOLVE_HRS2,EFFT_WEEKDAY2,7,EXCEPTION2,""<>YES"")/SATURDAY_HRS)
=0,"",1-(SUMIFS(EFFT_RESOLVE_HRS2,EFFT_WEEKDAY2,7,EXCEPTION2,""<>YES"")/SATURDAY_HRS))"

[SAT_UPTIME_METRICS1] = [SAT_UPTIME_METRICS1].Value

and the second:

[UPTIME_METRICS1] = _
"=if(SUMIFS(EFFT_RESOLVE_HRS2,STATE2,STATES1_1,EFFT_WEEKDAY2,""<>7"",DEVICE2,UPTIME_DEV1,EXCEPTION2,""<>YES"")/WEEKDAY_HRS)
=0,"",1-(SUMIFS(EFFT_RESOLVE_HRS2,STATE2,STATES1_1,EFFT_WEEKDAY2,""<>7"",DEVICE2,UPTIME_DEV1,EXCEPTION2,""<>YES"")/WEEKDAY_HRS))"

[UPTIME_METRICS1] = [UPTIME_METRICS1].Value