Calculating specifc byte positions in cells

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!

0
sdshadow (2)
1/4/2006 9:37:26 PM
excel 39879 articles. 2 followers. Follow

12 Replies
306 Views

Similar Articles

[PageSpeed] 7

=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)


<sdshadow@hotpop.com> wrote in message
news:1136410645.985915.16220@g49g2000cwa.googlegroups.com...
> 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!
>


0
bob.phillips1 (6510)
1/4/2006 10:25:24 PM
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

<sdshadow@hotpop.com> wrote in message 
news:1136410645.985915.16220@g49g2000cwa.googlegroups.com...
> 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!
> 


0
biffinpitt (3172)
1/4/2006 10:31:35 PM
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
-- 
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

<sdshadow@hotpop.com> wrote in message 
news:1136410645.985915.16220@g49g2000cwa.googlegroups.com...
> 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!
> 


0
bliengme5824 (3040)
1/4/2006 10:45:07 PM
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
sandymann2@mailinator.com
Replace@mailinator.com with @tiscali.co.uk


<sdshadow@hotpop.com> wrote in message 
news:1136410645.985915.16220@g49g2000cwa.googlegroups.com...
> 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!
> 


0
sandymann2 (1054)
1/4/2006 10:45:53 PM
some garbage was left over from my test - correc this to
=SUMPRODUCT(--(LEFT(B2:F2)="s"),--(RIGHT(TEXT(B2:F2,"00"))))

-- 
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Bernard Liengme" <bliengme@stfx.TRUENORTH.ca> wrote in message 
news:uPJ%23ECYEGHA.3820@TK2MSFTNGP12.phx.gbl...
>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
> -- 
> Bernard V Liengme
> www.stfx.ca/people/bliengme
> remove caps from email
>
> <sdshadow@hotpop.com> wrote in message 
> news:1136410645.985915.16220@g49g2000cwa.googlegroups.com...
>> 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!
>>
>
> 


0
bliengme5824 (3040)
1/4/2006 10:49:10 PM
"Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message 
news:OnrHF3XEGHA.3820@TK2MSFTNGP12.phx.gbl...
> =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
sandymann2@mailinator.com
Replace@mailinator.com with @tiscali.co.uk 


0
sandymann2 (1054)
1/4/2006 10:56:38 PM
You guys are awesome!

Thank you for the quick and easy responses!

0
sdshadow (2)
1/5/2006 4:31:05 AM
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?


-- 
Regards

Roger Govier


"Bernard Liengme" <bliengme@stfx.TRUENORTH.ca> wrote in message 
news:%23X8kVEYEGHA.1204@TK2MSFTNGP10.phx.gbl...
> some garbage was left over from my test - correc this to
> =SUMPRODUCT(--(LEFT(B2:F2)="s"),--(RIGHT(TEXT(B2:F2,"00"))))
>
> -- 
> Bernard V Liengme
> www.stfx.ca/people/bliengme
> remove caps from email
>
> "Bernard Liengme" <bliengme@stfx.TRUENORTH.ca> wrote in message 
> news:uPJ%23ECYEGHA.3820@TK2MSFTNGP12.phx.gbl...
>>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
>> -- 
>> Bernard V Liengme
>> www.stfx.ca/people/bliengme
>> remove caps from email
>>
>> <sdshadow@hotpop.com> wrote in message 
>> news:1136410645.985915.16220@g49g2000cwa.googlegroups.com...
>>> 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!
>>>
>>
>>
>
> 


0
roger5293 (1125)
1/5/2006 10:23:07 AM
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
-- 
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Roger Govier" <roger@technologyNOSPAM4u.co.uk> wrote in message 
news:O%237BHIeEGHA.140@TK2MSFTNGP12.phx.gbl...
> 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?
>
>
> -- 
> Regards
>
> Roger Govier
>
>
> "Bernard Liengme" <bliengme@stfx.TRUENORTH.ca> wrote in message 
> news:%23X8kVEYEGHA.1204@TK2MSFTNGP10.phx.gbl...
>> some garbage was left over from my test - correc this to
>> =SUMPRODUCT(--(LEFT(B2:F2)="s"),--(RIGHT(TEXT(B2:F2,"00"))))
>>
>> -- 
>> Bernard V Liengme
>> www.stfx.ca/people/bliengme
>> remove caps from email
>>
>> "Bernard Liengme" <bliengme@stfx.TRUENORTH.ca> wrote in message 
>> news:uPJ%23ECYEGHA.3820@TK2MSFTNGP12.phx.gbl...
>>>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
>>> -- 
>>> Bernard V Liengme
>>> www.stfx.ca/people/bliengme
>>> remove caps from email
>>>
>>> <sdshadow@hotpop.com> wrote in message 
>>> news:1136410645.985915.16220@g49g2000cwa.googlegroups.com...
>>>> 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!
>>>>
>>>
>>>
>>
>>
>
> 


0
bliengme5824 (3040)
1/5/2006 1:29:52 PM
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

"Roger Govier" <roger@technologyNOSPAM4u.co.uk> wrote in message 
news:O%237BHIeEGHA.140@TK2MSFTNGP12.phx.gbl...
> 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?
>
>
> -- 
> Regards
>
> Roger Govier
>
>
> "Bernard Liengme" <bliengme@stfx.TRUENORTH.ca> wrote in message 
> news:%23X8kVEYEGHA.1204@TK2MSFTNGP10.phx.gbl...
>> some garbage was left over from my test - correc this to
>> =SUMPRODUCT(--(LEFT(B2:F2)="s"),--(RIGHT(TEXT(B2:F2,"00"))))
>>
>> -- 
>> Bernard V Liengme
>> www.stfx.ca/people/bliengme
>> remove caps from email
>>
>> "Bernard Liengme" <bliengme@stfx.TRUENORTH.ca> wrote in message 
>> news:uPJ%23ECYEGHA.3820@TK2MSFTNGP12.phx.gbl...
>>>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
>>> -- 
>>> Bernard V Liengme
>>> www.stfx.ca/people/bliengme
>>> remove caps from email
>>>
>>> <sdshadow@hotpop.com> wrote in message 
>>> news:1136410645.985915.16220@g49g2000cwa.googlegroups.com...
>>>> 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!
>>>>
>>>
>>>
>>
>>
>
> 


0
biffinpitt (3172)
1/5/2006 8:16:07 PM
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" <roger@technologyNOSPAM4u.co.uk> wrote in message
news:O%237BHIeEGHA.140@TK2MSFTNGP12.phx.gbl...
> 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?
>
>
> -- 
> Regards
>
> Roger Govier
>
>
> "Bernard Liengme" <bliengme@stfx.TRUENORTH.ca> wrote in message
> news:%23X8kVEYEGHA.1204@TK2MSFTNGP10.phx.gbl...
> > some garbage was left over from my test - correc this to
> > =SUMPRODUCT(--(LEFT(B2:F2)="s"),--(RIGHT(TEXT(B2:F2,"00"))))
> >
> > -- 
> > Bernard V Liengme
> > www.stfx.ca/people/bliengme
> > remove caps from email
> >
> > "Bernard Liengme" <bliengme@stfx.TRUENORTH.ca> wrote in message
> > news:uPJ%23ECYEGHA.3820@TK2MSFTNGP12.phx.gbl...
> >>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
> >> -- 
> >> Bernard V Liengme
> >> www.stfx.ca/people/bliengme
> >> remove caps from email
> >>
> >> <sdshadow@hotpop.com> wrote in message
> >> news:1136410645.985915.16220@g49g2000cwa.googlegroups.com...
> >>> 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!
> >>>
> >>
> >>
> >
> >
>
>


0
terre081 (3244)
1/5/2006 9:10:06 PM
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>)

-- 
Regards

Roger Govier


"Peo Sjoblom" <terre08@mvps.org> wrote in message 
news:%23R31kxjEGHA.376@TK2MSFTNGP12.phx.gbl...
> 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" <roger@technologyNOSPAM4u.co.uk> wrote in message
> news:O%237BHIeEGHA.140@TK2MSFTNGP12.phx.gbl...
>> 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?
>>
>>
>> -- 
>> Regards
>>
>> Roger Govier
>>
>>
>> "Bernard Liengme" <bliengme@stfx.TRUENORTH.ca> wrote in message
>> news:%23X8kVEYEGHA.1204@TK2MSFTNGP10.phx.gbl...
>> > some garbage was left over from my test - correc this to
>> > =SUMPRODUCT(--(LEFT(B2:F2)="s"),--(RIGHT(TEXT(B2:F2,"00"))))
>> >
>> > -- 
>> > Bernard V Liengme
>> > www.stfx.ca/people/bliengme
>> > remove caps from email
>> >
>> > "Bernard Liengme" <bliengme@stfx.TRUENORTH.ca> wrote in message
>> > news:uPJ%23ECYEGHA.3820@TK2MSFTNGP12.phx.gbl...
>> >>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
>> >> -- 
>> >> Bernard V Liengme
>> >> www.stfx.ca/people/bliengme
>> >> remove caps from email
>> >>
>> >> <sdshadow@hotpop.com> wrote in message
>> >> news:1136410645.985915.16220@g49g2000cwa.googlegroups.com...
>> >>> 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!
>> >>>
>> >>
>> >>
>> >
>> >
>>
>>
>
> 


0
roger5293 (1125)
1/5/2006 11:11:36 PM
Reply:

Similar Artilces:

Cell Values Not There ???
I'm using VBA code in Excel 2007. I am having a strange problem. The cells have content. Some of them, but not all of them, are selected from Data Validation lists (drawn from a named range). However, when I try to access their values in VBA, it says they are empty!'' For example, the simple code: Debug.Print "Cell value is: " & ActiveSheet.Cells(2, 7).Value results in the output: Cell value is: I even tried the following code, to make sure it was not a problem with the ActiveSheet reference: ThisWorkbook.Sheets("Form").Cells(2, 7)....

Help getting SheetName into a cell
I need to refer to the sheet name in a formula and can't figure out how to do it. I can't find a Function which will do this. I did discover ActiveSheet.Name but I have been unsuccessful in getting it work in a function. Specifically I have sheets named "1.a" , "1.b" , "1.c" . . . "99.a" , "99.b" , "99.c" and need to get these names into cell C3 of each sheet. The sheets may not be in order and there may be missing sheets. Thanks in advance for any help. Omar Hi You could put somthing like this in a macro or attatch ...

% calculation
This always confuses me... What formula for 15% of 40,000 thanks Oooops, should have mentioned cells.. This always confuses me... What formula for 15% (L7) of 40,000 (L8) and answer in L9 thanks "S" <nonense@blueyonder.co.uk> wrote in message news:NZ9Hj.28495$w51.14942@fe2.news.blueyonder.co.uk... > This always confuses me... > What formula for 15% of 40,000 > > thanks > This is a multi-part message in MIME format. --------------070605030800000709050004 Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit...

Linked cells in Multiple Workbooks
I am tracking dates and have linked cells from another workbook so as t not have to duplicate the data entry of dates. The dates are linked t projects that are listed in rows and there is information in m workbook that is additional information within the row. So if someon changes the order of the other workbook, the information that is in m workbook does not get sorted with the order. Is there a way to link m cells to the linked cells? ie. Project ID Start Date Milestone EndDate Person Assigned 1234 (linked) 11/15/04 11/20/04(lin...

conditional formatting of row based on cell
I want to apply conditional formatting to a whole row based on the contents of one cell in that row. For example, if cell L2 contains the word 'sport', i want the whole of row 2 to be shaded. I'm guessing this is pretty simple, but seem to be having a bit of a mental blockage. Any suggestions would be appreciated. Thanks :) formula is =$l$2="sport" "Katherine" <katherine@katherine.com> wrote in message news:107b01c372ef$4ec59e30$a101280a@phx.gbl... > > I want to apply conditional formatting to a whole row > based on the contents of one...

Re: Is there a way to HIDE a row based on a value of a cell ?
Hi Reddance, Try instead: Sub Tester() Dim rng As Range Dim i As Long If LCase(Sheets("Ctrl").Range("A1").Value) _ <> "yes" Then Exit Sub End If With Sheets("ToPrint") For i = 5 To .UsedRange.Rows.Count Step 2 If Not rng Is Nothing Then Set rng = Union(rng, .Cells(i, "A")) Else Set rng = .Cells(i, "A") End If Next i End With If Not rng Is Nothing Then rng.EntireRow.Hidden = True End If ...

Last Cell
To All I was wondering if you could help me with my problem. If you have data in your worksheet and you say delete all the data in the last row, when you say F5 (Goto) "Special" and select "Last Cell" then it will always go to the data row that you have just deleted. There seems no way to get the "Last Cell" to update to where the last data is. Does anyone know to resolve this issue please. Thanks in advance. Deleting the data won't impact the "Last Cell". The "Last Cell" doesn't refer to the current data. It refers to the la...

Extend text to next cell
I am trying to get my text to run over to the next cell only in display not actual value of that cell. So if A1 says, "How now brown cow.", it should show over cells A2 and A3. However, I also need the carriage returns to be picked up. If I use the "Wrap Text" property it only shows the sentence in that cell. If I don't use it I get the [] (brackets) in place of the carriage return. Maybe you could merge those cells (A1:A3 or did you really mean A1:C1?) But be aware that merging cells causes trouble with sorting, pasting, and lots of other stuff. Format|cells|al...

Creating a list of info in A1 cell in multiple worksheets
How can I create a list of the info/contents in the A1 cell in multiple worksheets? I am trying to create a summary worksheet of the data from 90 worksheets and would like to be able to list the headers in the A1 cell vertically. 'Right click on the summary sheet tab, and paste in this macro. 'Edit where appropriate: Sub CreateSummary() 'Starting row for summary: i = 1 For Each Sheet In ThisWorkbook.Sheets If Sheet.Name <> Me.Name Then 'Control What column to place data in Me.Cells(i, "A").Value = Sheet.Range("A1").Value i = i + 1...

Data from non-adjacent cells as input for Linest
Hi, I would like to use data as input for Linest that is in the following cells: known y: A1 to A3, A10 to A12 known x: B1 to B3, B10 to B12 I have the feeling that this is almost answered in the following posts, but I do not understand it: http://groups.google.com/group/microsoft.public.excel/browse_thread/thread/fe4463f591797f44/4061a15313417929?lnk=gst&q=linest+#4061a15313417929 Regards, David ...

delay in displaying cell contents
Is there a simple function that can delay the display of the contents of a cell by a few seconds. I know that it can be done as VBA code but I dont know how to do it. Thanks in advance for any help. Dave Hi have a look at the Wait method in the VBA help -- Regards Frank Kabel Frankfurt, Germany "Dave" <dave-rawlins@beeb.net> schrieb im Newsbeitrag news:9995bde6.0410151425.1688ff24@posting.google.com... > Is there a simple function that can delay the display of the contents > of a cell by a few seconds. I know that it can be done as VBA code > but I dont know how...

Calculated Fields
Is there a way to use an IF or SUMIF function in a calculated field in a pivot table? When I try, I get all 0s as the result. Can you type a small sample of the pivot table layout and data, and explain what you'd like to calculate with a SUMIF formula? Jim C. wrote: > Is there a way to use an IF or SUMIF function in a > calculated field in a pivot table? When I try, I get all > 0s as the result. -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html Hi Jim, > Is there a way to use an IF or SUMIF function in a > calculated f...

Calculating Averages
Hi, I am using the newest version of Excel on Windows 7, and I need some help with a complex calculation. In cells A1-A100, I have dollar amounts ranging from $1-$1000. In cells B1-B100, I have percentages ranging from 0%-100%. I want to break out the cells in A1-A100 in several groups, like follows: $1-$99, $100-$199, $200-$299, and so on up to $1000. Then, I need to calculate the average percentage for jobs in those categories. So, for the category of $1-$99, lets say there are two cells with amounts in that range, A1 and A2. Their percentages in B1 and B2 are 40% and 6...

grouping cells to sort by only one column
Hi, New to this so please bear with me. I have a spread sheet where I am listing names in column A and othe info relating to that name in columns B and C in the two rows below th name. I then start a different name in the next row down with the info in t two rows below that, and so forth. I want to be able to add more names in the future and be able to sor all of this by column A only. The problem I am having is the info i the other columns moves. I need it to somehow attatch and stay put wit it's corisponding name without moving. In other words how do I make everything from A1 to ...

How do I link many cells to one particular cell? #2
I am trying to link multiple cells to one particular cell and can't seem to do it. I want the multiple cells not just to have the same information but be connected to the same cell. I want multiple cells to take the information from ex. cell D4. So everytime i change cell D4 every cell that is linked to it changes. Do I have to input =D4 into every single cell that I want to link or is there an easy way to mass link? enter =$D$4 in one cell and copy it to the rest. The $ keeps the address from changing relatively. Are the cells on the same worksheet? If yes, you could select...

Money using wrong date to calculate ROI YTD
I have opened a new file in Money to track investments for a stock club. I entered all transactions, which go back to Jan. 2002. When I look at performance for the account, the ROI YTD is not accurate, as Money is using values for the stocks going back only 6 months form the date I enterd the transactions rather than the values as of 1/1/03. What to do? In microsoft.public.money, Brooks wrote: >I have opened a new file in Money to track investments for >a stock club. I entered all transactions, which go back >to Jan. 2002. When I look at performance for the account, &g...

Unlocking Cells
Hi, I'd like to know if there's a simple way to unlock or lock cells. I'm not sure if "Lock" is the correct term here. For example, if you have a formula that has C$14. Is there an easy way to unlock (remove the "$") from the formula or to ad it without manually doing it? Thanks for your help! Lisa You can use a macro to change the reference style (absolute vs relative), but you can also do it manually. Select the portion of the formula (just a single cell reference or the entire formula if you want) and hit the F4 key to cycle through all 4 options. lj ...

How to fill cell with two combobox values?
I am looking for VB code for command button that fills two combo box values and a toggle value (Y/N) behind each other in one cell. Bart Excel 2003 ...

How do I restore the cell content window on my toobar?
tools options view ........formula bar -- Greetings from New Zealand Bill K "hutchc" <hutchc@discussions.microsoft.com> wrote in message news:4A53F690-97D4-43D6-A458-6AA82B95F575@microsoft.com... > From the View menu, choose Formula Bar hutchc wrote: -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html ...

How to quote cells when "IF" word is involved
On Sheet1, I have a data set (C1:C100) based on the input of cell A1. When A1 has various method choices,say,"method 1","method 2","method 3", each cell in the data set will have a new value accordingly. (=IF(A1="method 1",formula1,IF(A1="method 2",formula2,formula3)) I want to quote those three data sets on Sheet2. A lousy way is I could just duplicate Sheet1, set A1 to different value and quote data sets from different sheets. Is it possible to quote three data sets simultaneously from Sheet1? Thanks, Ming Perhaps use a 2 variable data t...

Sum of calculated controls in footers
I have a report with the following groupings: 1.) Claimant 2.) Group 3.) Carrier in the Claimant footer I have the a control named max_ded with the following calculation =Max([Ded]). I need to be able to sum this field at the "group level", and then sum the "group level sum" at the carrier level. I have tried to sum(max([Ded])) and I get error messages. I have tried Sum([max_ded]) etc. Any ideas would be greatly appreciated. Thanks ...

Insert string into a Cell ?
Hi, How do I insert string into a cell ? Dim myString As string myString = "abcd" shp.CellsSRC(visSectionProp, visRowProp, visCustPropsValue) = myString 'This will yield with type mismatch error. Thanks. Hello m, You've got a reference to the cell but not the property of the cell object. So if you want to set the cell's formula you need to use that property. Also, as you're adding a string you need to wrap it up in quotes (note two double quotes in a row get evaluated as a single set of quotes). Have a go with the following: shp.CellsSRC(visSectionProp, ...

Dividing a cell in Excel 2000
I am embarrassed to ask, but here goes. About a year ago some of you on this forum explained how I can divide a cell without creating a whole new row. I can't remember how I did it. With a diagonal line? A horizontal line? A vertical line? Doesn't really matter; I just want to enter a number on each side of the divide. Much appreciation, Lois You can always find previous posts in the archives. Just enter your name or email address in the author field at http://groups.google.com/advanced_group_search?q=group:*excel* to see your posts and any replies. You might take ...

Two cells divided into next cell
Hope this makes sense. I have F column that totals down to F16. Then I have a total on D16. I need both totals to be divided and show up on G16. Every cell has $ Amts, but the only cells not totaled down the row are E,G and J. So G16 is blank. Is this possible to do and If so how. I don't even know the formula to Divide and I need this to automatically happen every time I Insert the Amts in the cells. Please help In G16: =D16/F16 "Day" <Day@discussions.microsoft.com> wrote in message news:62C49FCC-273A-425B-9EEF-DCF2109A0DD9@microsoft.com... > > ...

Macro to copy cell down
I want to create a macro that will copy the cell above to the active cell. OR the built-in key that will do that tenbob@optonline.net Bob A few seconds with the macro recorder set to "Relative Reference" gave me this. Sub Macro1() ' ' Macro1 Macro ' Macro recorded 11/3/2003 by Gord Dibben ' ' Selection.Copy ActiveCell.Offset(1, 0).Range("A1").Select ActiveSheet.Paste Application.CutCopyMode = False End Sub Gord Dibben XL2002 On Tue, 04 Nov 2003 04:26:28 GMT, Bob K. <tenbob@optonline.net> wrote: >I want to create a mac...