How to find exact down time %

[gaurav das]

Dear Sir,

I am creating new Network costing file. And i am unable to find out down time % from overall sum of down time. Please help and guide me.

Attaching here excel file for your ref.

 

[SirJB7]

Hi, gaurav das!

You have to capitalize cells in column D in worksheet Sheet1 or change the formulas in P3 to:
=SI.ERROR(BUSCARV($C3&"-"&$B3&"-"&" "&MAYUSC($D3)&"-"&P$2&"-"&$F3&"-"&$E3;'Connectivity Down Time Tracker'!$A:$V;22;0);"") -----> in english: =IFERROR(VLOOKUP($C3&"-"&$B3&"-"&" "&UPPER($D3)&"-"&P$2&"-"&$F3&"-"&$E3,'Connectivity Down Time Tracker'!$A:$V,22,0),"")

However that wouldn't solve you problem since worksheet "Connectivity Down Time Tracker" should contains more than one entry for a given month site. If as site goes down half an hour every 4 hours, using VLOOKUP (or eventually INDEX) you'll be only getting the 1st entry for each day. The solution comes for using SUMIFS function (or SUMPRODUCT).

Hope it helps.

Regards!

 

[gaurav das]

Hi,
My problem is how can i get down time % in worksheet sheet 1 k3 and uptime % in J3. can any one give me solution for this.

 

[SirJB7]

Hi, gaurava das!

Following the previous indications would have led you to the straight solution.

Worksheet "Connectivity Down Trime Tracker":
L3: =DIA(R3) -----> in english: =DAY(R3)
M3: =TEXTO(R3;"MMMMM") -----> in english: =TEXT(R3,"MMMM")
Copy down L3:M3 as required.

Worksheet "Sheet 1":
E3: =VALOR(IZQUIERDA(E$1;4)) -----> in english: =VALUE(LEFT(E$1,4))
F3: "April" or the name of the related month
P3: =SUMAPRODUCTO(('Connectivity Down Time Tracker'!$D$3:$D$17=$C3)*('Connectivity Down Time Tracker'!$E$3:$E$17=$B3&"- "&MAYUSC($D3))*('Connectivity Down Time Tracker'!$N$3:$N$17=$E3)*('Connectivity Down Time Tracker'!$M$3:$M$17=$F3)*('Connectivity Down Time Tracker'!$L$3:$L$17=P$2)*('Connectivity Down Time Tracker'!$V$3:$V$17)) -----> in english: =SUMPRODUCT(('Connectivity Down Time Tracker'!$D$3:$D$17=$C3)*('Connectivity Down Time Tracker'!$E$3:$E$17=$B3&"- "&UPPER($D3))*('Connectivity Down Time Tracker'!$N$3:$N$17=$E3)*('Connectivity Down Time Tracker'!$M$3:$M$17=$F3)*('Connectivity Down Time Tracker'!$L$3:$L$17=P$2)*('Connectivity Down Time Tracker'!$V$3:$V$17))
Copy across P3 as required.
Copy down E3:F3 and P3:<last_column>3 as required.

Just advise if any issue.

Regards!