Formula to calculate specific data pasted into Excel

[bh24524]

Hello, I am looking for some kind of formula to quickly calculate percentage of Overtime to provide in a report to our General Manager. I run a report that gives me total hours in different categories but it would be nice if I could make some sort of template that I could simply paste those hours into and have a formula that would auto-calculate those hours. Hour types are pasted in A1 from the report

Day Reg:	2131.3​	Day OT:	1064.2​	Nite Reg:	2774.3​
Nite OT:	606.2​	DAY HOL:	744​	DAY DOUB:	423.7​
NITEDOUB:	282.6​	DAY VAC:	248​	NITE VAC:	696​
DAY SS:	96​	DAY PD:	50​	DAY BD:	10​
MED LV:	0.48​	ACC-SCK:	3.94​	Wrk-Comp:	1.44​
SH DVAC:	20​	SH NVAC:	37​	SP DVAC:	36​
SP NVAC:	8​	Vacation:	2.8​	Excused:	0.41​
Unexcuse:	0.62​	NITE HOL:	1048​	NITE SS:	124​
NITE PD:	66​	NITE BD:	32​	TOTAL:	10497.3​
Total % of OT

The formula would be in B13 and I am looking for the following:

Only sum the cells that have Day and Nite Reg hours, Day and Nite OT hours, and Day and Nite Doub hours. Then divide the sum of the OT and Doub hours(both day and nite of course) by that previous total.

When I calculated this out manually, I had gotten 33% OT if that will help test any formula.

Is there a formula that can encompass all of this with me simply pasting from the report I get it off of and without me having to rearrange cells?

 

[KRice]

When you obtain the report describing the information above, does it always have the same table structure? Put differently, will the six values you've described always be found in the same locations? If so, then would this work?

Book1
	A	B	C	D	E	F
1	Day Reg:	2131.3	Day OT:	1064.2	Nite Reg:	2774.3
2	Nite OT:	606.2	DAY HOL:	744	DAY DOUB:	423.7
3	NITEDOUB:	282.6	DAY VAC:	248	NITE VAC:	696
4	DAY SS:	96	DAY PD:	50	DAY BD:	10
5	MED LV:	0.48	ACC-SCK:	3.94	Wrk-Comp:	1.44
6	SH DVAC:	20	SH NVAC:	37	SP DVAC:	36
7	SP NVAC:	8	Vacation:	2.8	Excused:	0.41
8	Unexcuse:	0.62	NITE HOL:	1048	NITE SS:	124
9	NITE PD:	66	NITE BD:	32	TOTAL:	10497.3
10
11
12
13	% OT	0.326367
bh24524

Cell Formulas
Range	Formula
B13	B13	=SUM($D$1,$B$2,$F$2,$B$3)/SUM($B$1,$F$1,$D$1,$B$2,$F$2,$B$3)

You should be able to paste another data set directly over this one in cells A1:F9 and the calculation will refer to the new data. One additional thing to watch out for: It is possible that the desired hours data will be corrupted with hidden characters that cause them to appear as numeric values, but Excel might treat them as text. Whether this is an issue depends on how the data are generated; and if it is an issue, some additional steps would be necessary to clean the data after determining how it is corrupted. Post back with clarifications and other details if necessary.

 

[bh24524]

Hi there, yes the table structure will always be the same except for there won't always be "Day/Nite Doub" That only occurs during holidays, so if there's a way to incorporate it when its there and then calculate it when its not there, that would be ideal.

 

[KRice]

What do you mean by "there won't always be"? Will the labels be in the same cell locations, but the values would be blank as in the example below? If so, then it doesn't matter whether the values are blank--they won't affect the calculation. I've also offered an alternative, shorter formula that doesn't lock the cell locations (omits the $ signs) since they aren't necessary...just be careful if the formula is moved to another location to be sure that it refers to the correct locations.

Book1
	A	B	C	D	E	F
1	Day Reg:	2131.3	Day OT:	1064.2	Nite Reg:	2774.3
2	Nite OT:	606.2	DAY HOL:	744	DAY DOUB:
3	NITEDOUB:		DAY VAC:	248	NITE VAC:	696
4	DAY SS:	96	DAY PD:	50	DAY BD:	10
5	MED LV:	0.48	ACC-SCK:	3.94	Wrk-Comp:	1.44
6	SH DVAC:	20	SH NVAC:	37	SP DVAC:	36
7	SP NVAC:	8	Vacation:	2.8	Excused:	0.41
8	Unexcuse:	0.62	NITE HOL:	1048	NITE SS:	124
9	NITE PD:	66	NITE BD:	32	TOTAL:	10497.3
10
11
12
13	% OT	0.254015
14		0.254015
bh24524

Cell Formulas
Range	Formula
B13	B13	=SUM($D$1,$B$2,$F$2,$B$3)/SUM($B$1,$F$1,$D$1,$B$2,$F$2,$B$3)
B14	B14	=1-(B1+F1)/(B1+F1+D1+B2+F2+B3)

 

[bh24524]

So I mean that there wouldn't always be those hour types. In the case above, they wouldn't appear as zero's, they would simply not appear at all which would shift the other hour types. Thankfully though, Day Reg, Day OT, Nite Reg, and Nite OT will always appear in those spots. Given that day and nite Doub only appear during holidays, I am unsure if they would appear in the same exact spots.

I did try an alternative and it looks as follows now(sorry I don't have xl2bb - we aren't allowed to download things here at work otherwise I'd use it. I did label them and started to number the rows to help illustrate):

	A	B
1	Day Reg:	2456.10​
2	Nite OT:	663.00​
3	NITEDOUB:	285.70​
etc.	DAY SS:	104.00​
	DAY BD:	10.00​
	Wrk-Comp:	1.44​
	SP DVAC:	44.00​
	Excused:	0.41​
	NITE SS:	124.00​
	Day OT:	1107.80​
	DAY HOL:	846.00​
	DAY VAC:	248.00​
	DAY PD:	78.00​
	MED LV:	0.48​
	SH DVAC:	20.00​
	SP NVAC:	24.00​
	Unexcuse:	0.62​
	NITE PD:	66.00​
	Nite Reg:	3092.20​
	DAY DOUB:	439.60​
	NITE VAC:	856.00​
	DAY D-F:	10.00​
	ACC-SCK:	3.94​
	SH NVAC:	37.00​
	Vacation:	3.20​
	NITE HOL:	1154.00​
	NITE BD:	40.00​

Some of the numbers are different as we had some additions and subtractions to the hour types after I had posted this adjusting the overall percentage, but here is what I have so far:

The formula for this scenario is in cell E1:

=SUMPRODUCT((B1:B27)*((A1:A27="Day OT:")+(A1:A27="Day doub:")+(A1:A27="Nite OT:")+(A1:A27="nitedoub:"))/SUMPRODUCT((B1:B27)*((A1:A27="Day Reg:")+(A1:A27="Day OT:")+(A1:A27="Day doub:")+(A1:A27="Nite Reg:")+(A1:A27="Nite OT:")+(A1:A27="nitedoub:"))))

This is working ONLY if all the categories are in one column. Is this just how it would have to be? I don't fully understand the SUMPRODUCT function, but I thought initially that I could add additional ranges to encompass the different headers when they were spread among three columns. I thought by having something like =SUMPRODUCT((B1:B10,D1:D10,F1:F10)*((A1:A10,C1:C10,E1:E10="Day OT:")....and so on with the formula would allow this but it only results in an error. Are there any formulas you could have different ranges for hour types and hour values like that? If there is, that could make the original scenario doable and really ideal.

 

[KRice]

Ideally the data should be listed in a simple flat table to take full advantage of the capabilities offered in Excel. In this case, that would be a two-column data table input as shown in your last example...and that would make it convenient to use various functions (SUMIF and SUMPRODUCT for example). But you've indicated that you would prefer to operate on the default multi-range table...the one that splits the table into three ranges. This complicates matters. One approach is shown below: it requires searching each range (the combination of text field and value) for the text field of interest. To facilitate following the structure of the formula and for making revisions, I've inserted line breaks into the simpler, shorter formula I described earlier (also shown in cell B14), and then placed the value needed with a rather messy formula that relies on nested IFERROR and VLOOKUP functions to find the text field of interest, and upon finding it, returns the value associated with it. This should work no matter where the text field might occur in the three ranges. And if the "DOUB" fields are missing, or any other field for that matter, a 0 is returned and the percentage OT is computed based on the values present.

Book1
	A	B	C	D	E	F
1	Day Reg:	2131.3	Day OT:	1064.2	Nite Reg:	2774.3
2	Nite OT:	606.2	DAY HOL:	744	DAY DOUB:	432.7
3	NITEDOUB:	282.6	DAY VAC:	248	NITE VAC:	696
4	DAY SS:	96	DAY PD:	50	DAY BD:	10
5	MED LV:	0.48	ACC-SCK:	3.94	Wrk-Comp:	1.44
6	SH DVAC:	20	SH NVAC:	37	SP DVAC:	36
7	SP NVAC:	8	Vacation:	2.8	Excused:	0.41
8	Unexcuse:	0.62	NITE HOL:	1048	NITE SS:	124
9	NITE PD:	66	NITE BD:	32	TOTAL:	10497.3
10
11
12
13	% OT	0.32719817
14		0.32719817
bh24524

Cell Formulas
Range	Formula
B13	B13	=1-( IFERROR(VLOOKUP("Day Reg:",$A$1:$B$9,2,0),IFERROR(VLOOKUP("Day Reg:",$C$1:$D$9,2,0),IFERROR(VLOOKUP("Day Reg:",$E$1:$F$9,2,0),0)))+ IFERROR(VLOOKUP("Nite Reg:",$A$1:$B$9,2,0),IFERROR(VLOOKUP("Nite Reg:",$C$1:$D$9,2,0),IFERROR(VLOOKUP("Nite Reg:",$E$1:$F$9,2,0),0))) )/( IFERROR(VLOOKUP("Day Reg:",$A$1:$B$9,2,0),IFERROR(VLOOKUP("Day Reg:",$C$1:$D$9,2,0),IFERROR(VLOOKUP("Day Reg:",$E$1:$F$9,2,0),0)))+ IFERROR(VLOOKUP("Nite Reg:",$A$1:$B$9,2,0),IFERROR(VLOOKUP("Nite Reg:",$C$1:$D$9,2,0),IFERROR(VLOOKUP("Nite Reg:",$E$1:$F$9,2,0),0)))+ IFERROR(VLOOKUP("Day OT:",$A$1:$B$9,2,0),IFERROR(VLOOKUP("Day OT:",$C$1:$D$9,2,0),IFERROR(VLOOKUP("Day OT:",$E$1:$F$9,2,0),0)))+ IFERROR(VLOOKUP("Nite OT:",$A$1:$B$9,2,0),IFERROR(VLOOKUP("Nite OT:",$C$1:$D$9,2,0),IFERROR(VLOOKUP("Nite OT:",$E$1:$F$9,2,0),0)))+ IFERROR(VLOOKUP("DAY DOUB:",$A$1:$B$9,2,0),IFERROR(VLOOKUP("DAY DOUB:",$C$1:$D$9,2,0),IFERROR(VLOOKUP("DAY DOUB:",$E$1:$F$9,2,0),0)))+ IFERROR(VLOOKUP("NITEDOUB:",$A$1:$B$9,2,0),IFERROR(VLOOKUP("NITEDOUB:",$C$1:$D$9,2,0),IFERROR(VLOOKUP("NITEDOUB:",$E$1:$F$9,2,0),0))) )
B14	B14	=1-(B1+F1)/(B1+F1+D1+B2+F2+B3)

 

[bh24524]

Thank you so much. Wow that is one crazy formula! When I get some extra time I will definitely see if I can dissect it. It works like a charm though and this is easier than us having to repeatedly type numbers in the adding machine and then put them in an email. With this, it's a simple cut and paste and sending an email and we're done so thank you very much for that.

 

[KRice]

You're welcome. I'm glad to hear that it's working okay. To help with the formula dissection, you'll see that every line is similar...each is trying to find the value associated with one of the variable names needed for the calculation. The calculation follows the same form as shown in the formula in cell B14. Take the first such line in the formula where the value for "Day Reg:" is needed:
IFERROR(VLOOKUP("Day Reg:",$A$1:$B$9,2,0),IFERROR(VLOOKUP("Day Reg:",$C$1:$D$9,2,0),IFERROR(VLOOKUP("Day Reg:",$E$1:$F$9,2,0),0)))
This is looking for the variable name "Day Reg:" in any one of three different ranges: A1:B9, C1:D9, and E1:F9. The IFERROR function is used in a nested fashion with VLOOKUP to force the search to continue until a match is found, or to return a 0 (the last 0 shown) if the term "Day Reg:" is not found. VLOOKUP always searches the first column of the range specified in its argument list...so A1:A9, C1:C9, and E1:E9 are the searched regions. When the term is found, VLOOKUP returns the 2nd column of the range in its argument list (that's what the "2" means)...so B1:B9, D1:D9, and F1:F9 are the regions holding the value to be returned. You could shorten the formula slightly by eliminating the $ signs.