Calculating specifc byte positions in cells

[sdshadow]

Hi,

Here is what I am trying to do:

I have a spreadsheet that keeps track of employees vacation and sick
time.

The spreadsheet is formatted to column A is the employee name, columns
B - AB are days of the month.

I would like to have the manager be able to enter in the time off as
either S8 or V8 (8 representing the number of hours off the employee
had S = sick V = vacation).

I would like Column AC to then calcuate the total sick hours and AD to
calcuate the total of vacation hours.

I played around with a few formulas but could not figure out a way to
do it. Is there a way to have this done? What is the correct formula?

Thank you very kindly for the input!

 

[Bob Phillips]

=SUM(IF(LEFT(B2:AB2,1)="V",--SUBSTITUTE(B2:AB2,"V","")))

and

=SUM(IF(LEFT(B2:AB2,1)="S",--SUBSTITUTE(B2:AB2,"S","")))


which are array formulae, so commit with Ctrl-Shift-Enter


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Biff]

Hi!

Try this entered as an array using the key combo of CTRL,SHIFT,ENTER:

For vacation hours:

=SUM(IF(LEFT(B1:H1)="v",MID(B1:H1,2,10)*1))

Replace "v" with "s" for sick hours.

Biff

 

[Bernard Liengme]

A non-arrray solution
=SUMPRODUCT(--(LEFT(B2:AC2)="s"),--(--(RIGHT(TEXT(B2:AC2,"00")))))
the problem was what to do with empty cells - TEXT solved this
My solution works only when hours are one digit.
best wishes

 

[Sandy Mann]

If people are always sick or on Vacation for 8 hours then enter only V or S
and use:

=COUNTIF(B2:AB2,"S")*8

If they can be on sick for different numbers of hours try:

=SUM((LEFT(B24:AB24,1)="V")*(IF(ISNUMBER(--(RIGHT(B24:AB24,1))),RIGHT(B24:AB24,1),0)))

This is an array formula entered with Ctrl + Shift + Enter not just Enter

--
HTH

Sandy
(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Bernard Liengme]

some garbage was left over from my test - correc this to
=SUMPRODUCT(--(LEFT(B2:F2)="s"),--(RIGHT(TEXT(B2:F2,"00"))))

 

[Sandy Mann]

=SUM(IF(LEFT(B2:AB2,1)="V",--SUBSTITUTE(B2:AB2,"V","")))

and

=SUM(IF(LEFT(B2:AB2,1)="S",--SUBSTITUTE(B2:AB2,"S","")))


which are array formulae, so commit with Ctrl-Shift-Enter

I like it!

--
Regards

Sandy
(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[sdshadow]

You guys are awesome!

Thank you for the quick and easy responses!

 

[Roger Govier]

Hi Bernard (and others)

The OP has array solutions to his problem from Bob, Sandy and Biff,
which will solve his problem.

As someone who tries to avoid array formulae when possible, I was
examining your SUMPRODUCT solution.
I decided the following simple modification will deal with situations
(should they arise) of say 10.25 hours, whereas you stated your solution
dealt with single digit values. What I did was
=SUMPRODUCT(--(LEFT(B2:F2)="s"),--(MID(TEXT(B2:F2,"00"),2,5)))
and all appeared to be well and with data in B2:F2 of s2, v4, s10.25,
empty , empty,
it produced the desired result of 12.25

However, with just a single letter "s" entered into the first empty cell
in the range, I got a #VALUE error (as I did with your formula also).
This I can fully understand, and thought about ways of solving the
problem and came up with
=SUMPRODUCT(--(LEFT(B2:F2)="s"),--(IF(LEN(B2:F2)=1,0,MID(TEXT(B2:F2,"00"),2,5))))
This still produces a #VALUE result in the cell containing the formula,
but curiously when using F9 to evaluate parts of the formula in the
formula toolbar, I get the same result of 12.25.
Using F9 on the first part produces {1,0,1,1,0} and on the second part
produces {10,25,0,2,0,0}, F9 on the whole formula produces, as I have
said, 12.25.

Do you, or anyone else, have anything to suggest as to why this is, or
what the solution might be, purely out of academic interest?

 

[Bernard Liengme]

Hi Roger,
I tried your formula with a single SO in B2 (no digit) and also get #VALUE!
I use EXCEL 2003 so I tried the Evaluate Formula tool; after 6 steps I get
=SUMPRODUCT({1,1,1,0,0},--{"","2","5","0","4"}))
That null as the first item in the second array is the big problem - my
formula does the same thing.
Excel likes to treat blanks as zero (=A10+2 give 2 if A10 is empty) so why
does SUMPRODUCT not follow the general behaviour? I expect it is a C++
thing.
After 30 mins for playing I gave up.
best wishes

 

[Biff]

Hi Roger!

It's because you're using an IF on an array and even though that array is an
argument to Sumproduct you would still need to enter the formula as an
array.

Biff

 

[Peo Sjoblom]

Hi Roger,

this can be entered normally and as long as there are no more letters
involved than V or S it should work

=SUMPRODUCT(--(LEFT(B2:AB2,1)="S"),--(SUBSTITUTE("0"&SUBSTITUTE(UPPER(B2:AB2
),"V",""),"S","")))

upper is just there to guard against lower case since substitute is case
sensitive


--

Regards,

Peo Sjoblom

 

[Roger Govier]

Hi Peo

That is an excellent resolution to the problem (not that it was my
original problem), but as I said I became interested from an academic
viewpoint of resolving it with Sumproduct after I realised that
Bernard's solution failed in the case of a single letter having been
entered erroneously into a cell. Your solution Peo, not only resolves
that issue, but also deals with hours of double integer and double
decimal values.

Thank you also Biff, for your observation, which is quite correct.
I went through the Evaluate procedure in XL2003, and realised that the
formula "choked" because after the first resolution of the IF() clause,
it ceased, therefore there were 2 arrays of different sizes being
resolved, hence the #VALUE error. Entering the formula as an array
formula resolved that issue, but there were already satisfactory array
solutions.

The thing that still puzzles me however, is the fact that using F9 in
the formula bar gives a proper resolution to the problem, with the IF
part within the Sumproduct being continually evaluated so that the 2
arrays produced are of equal size and therefore a correct answer ensues.
Yet, when as a formula in the cell, the whole evaluation "chokes" as
outlined above, and produces the #VALUE error.

I am still curious to know why one "method" works, and the other
doesn't, with the same formula.

Thank you both (and Bernard) for trying to throw further light on this
rather curious phenomenon.

Will there ever be a day when I truly understand Excel? (No prizes for
replies to this rhetorical question <vbg>)