Edward Pamias
asked on
I need a formula to display .5 instead of 1 for a counta formula.
I need a formula if it sees HD in a cell it counts it as .5 and the other characters are counted as 1.
V= 1
P = 1
S = 1
H= 1
HD = .5
TI = 1
V= 1
P = 1
S = 1
H= 1
HD = .5
TI = 1
Another option:
=COUNTIF(B3:B8,"<>HD")+COU NTIF(B3:B8 ,"HD")/2
=COUNTIF(B3:B8,"<>HD")+COU
Another option to be dynamic:
Set up a list with the value against each option eg:
V 1
P 1
S 1
H 1
HD 0.5
TI 1
Then against your list of values use a lookup to assign each row with a value.
=VLOOKUP(B3,$J$3:$K$8,2,FA LSE)
B3 being the text value, J3:K8 the list of values for each option.
You can then use:
=SUMPRODUCT(B3:C8)
Or just:
=SUM(C3:C8) when text values are in B and lookup in C.
Set up a list with the value against each option eg:
V 1
P 1
S 1
H 1
HD 0.5
TI 1
Then against your list of values use a lookup to assign each row with a value.
=VLOOKUP(B3,$J$3:$K$8,2,FA
B3 being the text value, J3:K8 the list of values for each option.
You can then use:
=SUMPRODUCT(B3:C8)
Or just:
=SUM(C3:C8) when text values are in B and lookup in C.
ASKER
I just found out its a Excel Template Sheet they are using called Employee Absence Schedule. They modified the sheet and added half day for one of the absent key types. So I need the formula under Total days modified to reflect the new absent key.
If you can upload the file we can put the relevant formulas in place.
You will need to remove the real names/details as this is a Public site and the data can be shared across the whole site.
You will need to remove the real names/details as this is a Public site and the data can be shared across the whole site.
ASKER
Please see attached. Thank you.
Employee-absence-schedule1_Sample.xlsx
Employee-absence-schedule1_Sample.xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks all.
=(COUNTA(A1:A1000)-(COUNTI