Enter specific letter and return letter + formula

TheSubject

New Member
Joined
Feb 16, 2016
Messages
18
HI all, hopefully someone can help me out. I submit weekly payroll for work, and however many years in, it's turn out my HR department can't read the payroll documents and rather than doing their job, they're asked us to double enter a load of data.

So, We have columns with employee names, rows with days, and then below the days, we have a total hours row, a holiday row, a furlough row, etc...
Traditionally I'd just put an H for holiday in the day cell, with numbered details below of many hours, it's usually a count if multiplied by standard shift length 7 hours formula, but we do edit that sometimes (e.g. salaried staff getting no paid over time, so if they do overtime before a holiday I would take it off the used holiday hours rather than them doing unpaid overtime). However, it's come about that HR was only reading the H and counting it as 7 regardless, so where we had run through just an hour or two, HR was taking a whole days holiday, which explains years of arguments over remaining holiday allowances.

Similarly with Furlough, we'd put an F, but accounts asked for more details because where month end and week end don't line up, we've got to make sure furloughs going through in correct months etc, so I have the furlough row at the bottom which does (contracted hours - total hours worked, rounded to 1/2 an hour) we have manually been writing e.g. 8F, 9F, so payroll gets done correctly.

HR has now spotted this and asked for 3H, 7H etc etc.

Now Furlough was a pain but its nearly over. Holiday is not.

So the formula I want is something like =if(F, "F"&(cell ref)/count of Fs in column),(if = H, "H"& (cell ref)/count Hs in column)

Problems I can see being that we just want to enter the F or H, so it has to be in conditional formatting rather than entering a formula?
Also when I made my first few attempts, I was getting into circular references. I can do a hidden how much holiday in 15, divided by number of Hs, then all I need is a conditional "If H, then "H"& hidden cell..." but I can't see how to make that happen.

Is this doable? Can I do it? Picture below, so column C is actually wrong - 9F, 8F and 9F do not equal the 25.5 below in row 16. Human mistake, but this is what I'm trying to avoid. So all Fs and Hs, need to automatically turn themselves into the letter & an average of the number in row 15 or 16, split across however many Hs or Fs there are in the column.


to uplaod.jpeg
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,694
Office Version
  1. 365
Platform
  1. Windows
For starters, conditional formatting can only change how you see what is already there, it can not be used to change the actual content of the cell (for example, it can not convert a letter to a number), or to add content to an otherwise empty cell.

Your description of your problem makes little sense so this is based only on the last line of your post, see if it gets you heading in the right direction. Note that if your version of excel doesn't support dynamic arrays then you will need to array confirm this with Ctrl Shift Enter. This can not be done on a mobile device.
Book1 (version 2).xlsb
D
5DO
69F
76
86
98F
109F
112.5
12
13
14
15
1626
Sheet11
Cell Formulas
RangeFormula
D16D16=SUMPRODUCT(IFERROR(--SUBSTITUTE(D5:D11,"F",""),0)*(RIGHT(D5:D11)="F"))
 

Watch MrExcel Video

Forum statistics

Threads
1,130,342
Messages
5,641,592
Members
417,224
Latest member
llama9207

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top