Re: separate cell values with formulas - can this be improved?
- From: ilia <iasafiev@xxxxxxxxx>
- Date: Wed, 08 Aug 2007 14:07:51 -0700
OK, I think I got it. I managed to achieve row independence from the
helper column version, enabling me to make it one big ugly array
formula. It assumes either a comma following the last value, or " = "
<space>=<space>.
For weekdays:
=NETWORKDAYS($B$22,$D$22)-SUM(IFERROR(--(WEEKDAY(DATE(YEAR($B
$22),MONTH($B$22),MID($F$22,IFERROR(FIND(CHAR(150),SUBSTITUTE($F
$22,",",CHAR(150),ROW(1:31)-1))
+1,1),IFERROR(FIND(CHAR(150),SUBSTITUTE($F
$22,",",CHAR(150),ROW(1:31))),FIND(" = ",$F$22,1))-
IFERROR(FIND(CHAR(150),SUBSTITUTE($F$22,",",CHAR(150),ROW(1:31)-1))
+1,1))),2)<6),""))&" School Days (10hrs per day)"
For weekend days:
=($D$22-$B$22+1)-NETWORKDAYS($B$22,$D$22)-SUM(IFERROR(--
(WEEKDAY(DATE(YEAR($B$22),MONTH($B$22),MID($F
$22,IFERROR(FIND(CHAR(150),SUBSTITUTE($F$22,",",CHAR(150),ROW(1:31)-1))
+1,1),IFERROR(FIND(CHAR(150),SUBSTITUTE($F
$22,",",CHAR(150),ROW(1:31))),FIND(" = ",$F$22,1))-
IFERROR(FIND(CHAR(150),SUBSTITUTE($F$22,",",CHAR(150),ROW(1:31)-1))
+1,1))),2)>5),""))&" Weekend days (16hrs per day)"
It's not as elegant as the others, and it still won't work if preceded
by text. Also, again it relies on Excel 2007 for IFERROR
functionality. However, I only see 5 levels of nesting so by
replacing IFERROR with IF(ISERROR()) it could probably work in earlier
versions.
On Aug 8, 4:32 pm, ilia <iasaf...@xxxxxxxxx> wrote:
Yes I see what you're saying. That string is typed in by hand, so a
comma can be added theoretically
Using this formula:
=NETWORKDAYS(B22,D22)-
SUM(IF(ISNUMBER(FIND(","&DAY(ROW(INDIRECT(B22&":"&D22))),","&F22)),IF(WEEKDAY(ROW(INDIRECT(B22&":"&D22)),
2)<6,1)))
F22 = 1,2,3,6,9,10,12,15 = 8 days)
I get the correct result for weekdays, but only if F22 value begins
with the first day. 1 and 15 are weekend days; thhe rest are
weekdays. Month is July, so there is a total of 31 days. The result
I get is 16 weekdays, 7 weekends. This is correct.
If I add anything in front of the text, the first value is omitted
because of the comma added. But I don't see how adding a comma after
the last value affects this formula? The find function is looking for
anything preceded by the comma.
Another problem with it is in the case of a value being in the second
10 days. For instance, if
F2 = 12,15 = 2 days)
The formula will evaluate both ",1" and ",12" resulting on one extra
weekend (in case of July 2007, the 1st). The new formula does work at
avoiding this problem, so it looks like adding a comma at the end
might well be the solution.
Sorry, just brain dumping. This is an interesting problem and I'm
still figuring out what's the best way to set it up, so as to develop
accurate procedures for data entry.
Thanks all.
-Ilia
On Aug 8, 1:38 pm, "T. Valko" <biffinp...@xxxxxxxxxxx> wrote:
I found a bug in those formulas.
F22 = 1,2,3,6,9,10,12,15 = 8 days)
How is that string generated?
You would need to delimit each day with a comma from the rest of the string.
In the above there is no comma after the 15 and that is causing the bug.. So,
F22 needs to look like this:
1,2,3,6,9,10,12,15, = 8 days)
Then, the slightly modified formulas:
=NETWORKDAYS(B22,D22)-SUM(IF(ISNUMBER(FIND(","&DAY(ROW(INDIRECT(B22&":"&D22)))&",",","&F22)),IF(WEEKDAY(ROW(INDIRECT(B22&":"&D22)),2)<6,1)))&"
Weekdays(10hrs per day)"
=(D22-B22)+1-NETWORKDAYS(B22,D22)-SUM(IF(ISNUMBER(FIND(","&DAY(ROW(INDIRECT(B22&":"&D22)))&",",","&F22)),IF(WEEKDAY(ROW(INDIRECT(B22&":"&D22)),2)>5,1)))&"
Weekends(16hrs per day)"
--
Biff
Microsoft Excel MVP
"ilia" <iasaf...@xxxxxxxxx> wrote in message
news:1186591569.398203.216580@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
OK, first a correction to my original helper column. I'm making heavy
use of the new IFERROR function, so bear with me if you're using an
earlier version.
=MID($F$22,IFERROR(FIND(CHAR(150),SUBSTITUTE($F
$22,",",CHAR(150),ROW()-1))+1,1),IFERROR(FIND(CHAR(150),SUBSTITUTE($F
$22,",",CHAR(150),ROW())),FIND(CHAR(150),SUBSTITUTE($F$22," =
",CHAR(150),1)))-IFERROR(FIND(CHAR(150),SUBSTITUTE($F
$22,",",CHAR(150),ROW()-1))+1,1))
This assumes that if a comma is not found, the next separator will be
" = ". There are two problems with this: (1) it relies on this list
always starting in the first row, and (2) it is unable to omit any
text prior to the first day number (in case I want to combine E22 and
F22, for example).
The two array formulas proposed instead work (thanks Biff). I think
I'm going to end up doing that. I couldn't get the non-array versions
to work.
By the way, I'm not accounting for holidays because this is a 24/7/365
service (366 this fiscal year). I do use the holidays argument for
NETWORKDAYS on other types of invoices.
-Ilia
On Aug 8, 4:23 am, "Héctor Miguel" <NOhemiordiS...@xxxxxxxxxxxxxx>
wrote:
hi [again], guys !
using the *tricky* named formula for nDays...
=sumproduct(--(weekday(date(year(b22),month(b22),ndays),2)<6))&" weekdays
(10 hrs per day)"
=sumproduct(--(weekday(date(year(b22),month(b22),ndays),2)>5))&" weekends
(16 hrs per day)"
hth,
hector.
__ previous posts __
[I guess] you could by-pass the helper column (J) and *construct* an
array constant by...
using named-formulae [insert > name > define...] and the ancient
'evaluate' xl4-macro function [i.e.]
[assuming you need ONLY the *days* portion of a single-cell $F$22]
name: nDays
formula: =evaluate("{"&left(!$f$22,search(" ",!$f$22)-1)&"}")
note the signs '!', it is important !!!
now, you can substitute in your array-formulae any reference to range
J1:J31 with *the name* [nDays]
NOTES:
a) there is a possibe 'risk' of an xl-crash... while defining names as
suggested...
if you copy the worksheets that uses them [at least, in xl-97 & 2000]
so...
use this proposal on your own... risk, criteria, modifications, etc.
b) also, if you need this procedure to be used in NON-english xl
versions -?-
you will need to find out the character for rows separator in
constant arrays by...
defining another named formula [i.e.]
name: rS
formula: =index(get.workspace(37),15)
and change the formula for nDays name to:
=evaluate("{"&substitute(left(!$f$22,search("
",!$f$22)-1),",",rs)&"}")
[just wild ideas]
hth,
hector.
__ original post __
I am working with a monthly invoice. On the invoice, we have Period
Start and Period End date.
These will usually be first and last of the month.
We are billing for a service whose rate varies based on whether it is a
weekend or a weekday.
We receive information from service department regarding which days
services were not performed, and list them on the invoice.
Here's an example (without the " "s):
(stored as dates)
B22 = July 1, 2007
D22 = July 31, 2007
(text entries)
E22 = "(Off on July "
F22 = "1,2,3,6,9,10,12,15 = 8 days)"
Now, we need to adjust number of billable days based on whether each
day is a weekday or a weekend.
In cells J1:J31, i have this formula:
=MID($F$22,IFERROR(FIND(CHAR(150),SUBSTITUTE($F$22,",",CHAR(150),ROW()-1))
+1,1),FIND(CHAR(150),SUBSTITUTE($F$22,",",CHAR(150),ROW()))-IFERROR(FIND(CHAR(150),
SUBSTITUTE($F$22,",",CHAR(150),ROW()-1))+1,1))
This extracts each number (1,2,3,6,9,10,12,15).
Now, in the main portion of the invoice I have these two array
formulas:
=NETWORKDAYS(B22,D22)-SUM(IFERROR(--(WEEKDAY(DATE(YEAR(B22),MONTH(B22),J1:J31),2)<6),""))&"
Weekdays (10hrs per day)"
and
=(D22-B22)+1-NETWORKDAYS(B22,D22)-SUM(IFERROR(--(WEEKDAY(DATE(YEAR(B22),MONTH(B22),J1:J31),2)>5),""))&"
Weekends (16hrs per day)"
My question is: can I do this somehow without using the helper column
(J)?
I couldn't think of another way to find each subsequent comma, perhaps
there is a more clever way of using the SUBSTITUTE function?
I would like this to be a template that anyone can use, and have to do
nothing besides fill in the two dates and the non-service days.
While it's not a problem hiding the J column, there's still a chance it
might get deleted or rows inserted to screw up the calculation.- Hide
quoted text -
- Show quoted text -- Hide quoted text -
- Show quoted text -- Hide quoted text -
- Show quoted text -
.
- References:
- separate cell values with formulas - can this be improved?
- From: ilia
- Re: separate cell values with formulas - can this be improved?
- From: Héctor Miguel
- Re: separate cell values with formulas - can this be improved?
- From: Héctor Miguel
- Re: separate cell values with formulas - can this be improved?
- From: ilia
- Re: separate cell values with formulas - can this be improved?
- From: T. Valko
- Re: separate cell values with formulas - can this be improved?
- From: ilia
- separate cell values with formulas - can this be improved?
- Prev by Date: Re: separate cell values with formulas - can this be improved?
- Next by Date: Re: separate cell values with formulas - can this be improved?
- Previous by thread: Re: separate cell values with formulas - can this be improved?
- Next by thread: Re: separate cell values with formulas - can this be improved?
- Index(es):
Relevant Pages
|
