Forum Discussion
Trying to calculate sum with letter in the front
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.
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?
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.
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.
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)
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:
- So if someone has an "O" that means they only work 4 days for 7.5 hrs/day instead of 8hrs/day?
- correct
