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 [TABLE="class: grid, width: 250, align: left"]
<TBODY>[TR]
[TD][/TD]
[TD="align: center"]UPTIME %[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]SLA = [/TD]
[TD="align: center"]99%[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]SEVERITY =[/TD]
[TD="align: center"]ALL[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]DEVICE =[/TD]
[TD="align: center"]TAB[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]SATURDAY[/TD]
[TD="align: center"]56.67%[/TD]
[TD="align: center"]<---SATURDAY FORMULA[/TD]
[/TR]
[TR]
[TD="align: center"]TOTAL[/TD]
[TD="align: center"]100%[/TD]
[TD="align: center"]<---NEED TO CREATE[/TD]
[/TR]
[TR]
[TD="align: center"]AR[/TD]
[TD="align: center"]100%[/TD]
[TD="align: center"]<---REG FORMULA[/TD]
[/TR]
[TR]
[TD="align: center"]AZ[/TD]
[TD="align: center"]100%[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]CA[/TD]
[TD="align: center"]100%[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]CT[/TD]
[TD="align: center"]100%[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]DC[/TD]
[TD="align: center"]100%[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]DE[/TD]
[TD="align: center"]100%[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]FL[/TD]
[TD="align: center"]100%[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]GA[/TD]
[TD="align: center"]100%[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]IA[/TD]
[TD="align: center"]100%[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]KS[/TD]
[TD="align: center"]100%[/TD]
[TD="align: center"][/TD]
[/TR]
</TBODY>[/TABLE]

AFTER
[TABLE="class: grid, width: 250, align: center"]
<TBODY>[TR]
[TD][/TD]
[TD="align: center"]UPTIME %[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]SLA = [/TD]
[TD="align: center"]99%[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]SEVERITY =[/TD]
[TD="align: center"]ALL[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]DEVICE =[/TD]
[TD="align: center"]TAB[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]SATURDAY[/TD]
[TD="align: center"]56.67%[/TD]
[TD="align: center"]<---SATURDAY FORMULA[/TD]
[/TR]
[TR]
[TD="align: center"]TOTAL[/TD]
[TD="align: center"]100%[/TD]
[TD="align: center"]<---NEED TO CREATE[/TD]
[/TR]
[TR]
[TD="align: center"]AR[/TD]
[TD="align: center"]100%[/TD]
[TD="align: center"]<---REG FORMULA[/TD]
[/TR]
[TR]
[TD="align: center"]AZ[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]CA[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]CT[/TD]
[TD="align: center"]100%[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]DC[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]DE[/TD]
[TD="align: center"]100%[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]FL[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]GA[/TD]
[TD="align: center"]100%[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]IA[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]KS[/TD]
[TD="align: center"]100%[/TD]
[TD="align: center"][/TD]
[/TR]
</TBODY>[/TABLE]
​

 

[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