SOLVED

Trying to calculate sum with letter in the front

Copper Contributor

Lucy_McMahon_1-1662741448919.png

Green cells are filled with 8 automatically. Entries into other cells subtract 8 automatically instead of what is entered, since there is text as well. I'm trying to get it to subtract the number after any given letter instead of 8 so I don't have to do the math manually. 

16 Replies

@Lucy_McMahon 

 

Could you back up a bit and tell us more of what the "big picture" is here? I assume this plays some part in the process of determining what a person will get paid; but do the letters also indicate types of leave, or other conditions that may be paid in some cases but attributed to, say, training or travel?

 

Second, I'm confused by what appear to be discrepancies between what I see and what you describe. If we're to subtract the numbers after the letters from the default value of 8, why does the person in the top two rows get credited with only 32 hours worked--that would correspond to the four totally green cells, yes, but it certainly doesn't take into account the hours remaining after subtracting the various hours preceded by the letter A.  So maybe you could explain "the math" that you are not wanting to do manually. What would it be in that case?

@Lucy_McMahon 

 

It's fairly easy to extract the value, as a number, from a text field like A5 or P3.5. Here's a formula that will do that for a combination in cell A3. [Note: I've made an assumption here that there is always only ONE single letter before the numbers, no matter how many numbers.]

=VALUE(RIGHT(A3,LEN(A3)-1))

See the attached spreadsheet.

 

But note, this is just a small part of what  you're asking in your original post. I still would want to understand the big picture into which this small part of the puzzle is to fit.

@mathetes 

 

Yes, the letters indicate different kinds of leave. The letters are not essential for this calculation but are used elsewhere so I can't remove them. We are tabulating data on each kind of leave used but I need the two week period to calculate the hours correctly first. The formula is inaccurate for what I have so far. 

 

This is because each green cell has the number 8 in the same color text at the fill to default a full 8 hour workday. Any entry in the B:F columns -as of right now- substracts the default 8 from the sum. Which works fine if someone took the whole day off. But I ran into the problem with partial requests (i.e. A1.5). This file was only being used for my own ease of access for timecard processing so I was just doing the math manually should a partial day off occur. But the powers that be have seen it and want me to expand the usage. So now I need to find a way for it to accurately calculate so I don't have to manually subtract the leave taken from the 80 hour work week for all 40 people in our department.

 

I am perfectly happy to remove the default 8 in the B:F columns if I can figure out a way to sum the H column correctly. For ease, the H column would look like =80-sum(complicated formula to ignore text in cell range and tabulate numbers in range), The cells with no leave entries would just be green but with no hidden numerical data. Ideally I wouldn't need an additional column for the output as in extracting the value. I was trying to figure out an array formula but the cell is merged so it won't work with it as. 

actually I think that exactly explains the top and bottom bi-week rows. They don't have the formula to add/subtract the partial hours so the "worked hours" only takes into account the 'green' cells right now. The middle 2 rows are my question with how to handle the "O" and how those rows make 60 hrs scheduled and 45hrs worked.
a simple answer to how to get numbers after 1 letter is = --mid(A1,2,99)
but you will get errors and stuff and probably need to add IFERROR() around it and still do something about that "O" I suspect.
and by the way, I always recommend avoiding merged cells whenever possible. I would highly recommend you consider just adding 5 more columns for the second week. Those columns are already almost 2x wider than necessary so if you added 5 more but made them more narrow the overall width won't change much and you can have each employee on 1 row and solve a LOT of present and future issues.

The O is for off. Those people only work for 60 hours a week. If I can get the formula for 80 hours I can just adjust it for 60. Those 2 cells that say O won't be included in the range. 

So if someone has an "O" that means they only work 4 days for 7.5 hrs/day instead of 8hrs/day?

@Lucy_McMahon   @mtarler 

 

To follow up with Matt's suggestions, he's right, first, to recommend against merged cells (they can look nice to humans viewing the spreadsheet, but can have unintended negative consequences when it comes to spreadsheet performance). The idea of taking two weeks and laying them out horizontally is very viable, would put each employee on one row, giving a different kind of clarity.

 

I suspect, further, that he and I would both recommend against making a modified formula to handle the 60 hour weeks of those who have (in the example you posted) Friday's off. What you want is a single formula that has been tested for accuracy and that handles all cases, all types of leave, all types of work weeks.

 

Having worked in my professional career (retired now for almost exactly 20 years) I worked several years as director of the HR/Payroll database of a major corp. So this kind of data and the processing of it was an everyday matter.

 

So to help us help you come up with a layout and viable formula(s) that cover the range of situations, would you be willing to spell out the various codes (types of leave) and the rules around them if they're in any way different from just adding up hours so coded. 

 

I'm assuming in this case we're dealing with bi-weekly payroll, right?

Would it be helpful to have a range of cells that clearly spells out for each employee, for each bi-weekly period:

  • Hours scheduled
  • Hours worked
  • Hours on Leave type A
  • Hours on Leave type B.... (etc)
  • Other hours (please be specific)

 

 

@Lucy_McMahon 

 

mathetes_0-1662755749647.png

 

Wha?! Wait!! Where did 7.5 hour days come from? I thought we were dealing with 8 hour days!

@mathetes 

 

Lucy_McMahon_0-1662755907244.png

 

If it will be helpful, I can put each person on one row with 10 columns, instead of how it is currently. It would take while as I have 28 sheets with 35 or so people that are already filled in with the data to avoid the merged cells. Then I can use array formulas. The letters are already coded but are only consequential in the YTD calculations:

 

Lucy_McMahon_1-1662756153129.png

 

 

 

 

Yes, we have 3 employees who don't work all 10 days. They only work 8 days @ 7.5 hours a day for 60 hours in the bi-weekly pay period.
best response confirmed by Hans Vogelaar (MVP)
Solution

@Lucy_McMahon The attached is one option.  I used 10*countif("O") to adjust from 80 to 60 hours but that is obviously very case specific to this case.  Then I use a subtraction.  Note I also formatted it all as a table so all the formulas autofill all the rows (instead of an array formula) but could be reformatted not using a table.  I also added conditional formatting to automatically color those columns based on the letter entered (or blank) but only had a subset at the time.

maybe you already know this and use it but certain formulas like sum can be used as a 3-d formula meaning you can sum across multiple sheets as a range. SO if you do these sums for each employee on the same location on each of the bi-weekly sheets then the YTD formula is simply ='1st Sheet':'Last Sheet'!M2 if that value is located at M2 on each of those sheets

@Lucy_McMahon 

 

It would take while as I have 28 sheets with 35 or so people

 

This could be a minor diversion, but I need to ask about it.

 

I'm a believer in keeping to single databases in Excel unless there are compelling data processing reasons to do otherwise. We often come across people here in the forum who have arranged workbooks with separate sheets to represent, say, different departments, or different product lines. It makes sense to do that when we're working with paper ledger sheets; it helps us as humans keep things organized and clear. But when it comes to the computer, when it comes to Excel, that kind of organization can actually get in the way of some of Excel's remarkable abilities to summarize and work with data.

 

Given the number 28 in your case, I'm going to make a guess that you have a separate sheet for each pay-period, allowing for a week of overlap each--with the prior year and the following year. It's a guess. Maybe there's another reason. Again, unless there's a compelling reason to do it that way, I'd recommend making all of those sheets into one large table, just adding a column to reflect which pay period each row represents. It will then be a lot easier to do such things as weekly comparisons, annual summaries of leaves by type, by employee, etc.

 

As I said at the start, this may just be a minor diversion, but asking the question is part of what I do as a "consultant" in this case. I don't want just to answer your presenting question about formulas; I also want to make sure you're using Excel to its maximum capabilities.

@mtarler 

 

Thank you so much! This worked perfectly. 

1 best response

Accepted Solutions
best response confirmed by Hans Vogelaar (MVP)
Solution

@Lucy_McMahon The attached is one option.  I used 10*countif("O") to adjust from 80 to 60 hours but that is obviously very case specific to this case.  Then I use a subtraction.  Note I also formatted it all as a table so all the formulas autofill all the rows (instead of an array formula) but could be reformatted not using a table.  I also added conditional formatting to automatically color those columns based on the letter entered (or blank) but only had a subset at the time.

View solution in original post