Aladin, any comments?
Posted by Aladin Akyurek on March 26, 2001 9:37 AM
No doubt worth trying. Sounding perhaps a little unconvinced here. In a teaching situation I try not to overload the learner. I think the second year students can grasp such an idea. I'd like to see them to apply the custom number format that you suggest in the output phase, not in the processing.
Yeah. Why not. I'd like that.
Aladin
P.S. I was trying to post here... Please keep any continuation here.
Posted by Mark W. on March 26, 2001 10:00 AM
Here's a full blown example of this design concept...
Setup for an account receivables data list:
Enter the following into cells A1:D6...
{"Name","Gender","Marital Status","Acct Balance"
;"Tom Smith",1,0,300
;"Jane Doe",0,1,0
;"Mary Greene",0,0,125
;"Larry White",1,1,450
;"William Jones",1,0,0}
Column B, "Gender", is formatted as:
[=1]"Male";[=0]"Female";General and left aligned.
Column C, "Marital Status", is formatted as:
[=1]"Married";[=0]"Single";General and left aligned.
Named Ranges:
Male refers to B2:B6
Married refers to C2:C6
hasBalance refers to D2:D6 as does Acct_Balance
Male_Acct_Balances refers to = Male*Acct_Balance
First, let's create a "Title" field in column E.
Just enter the formula, =IF(Male,"Mr.",IF(Married,"Mrs.","Miss")),
into E2 and copy down.
Next, let's create some summary statistics.
{=SUM((Male*NOT(Married))+Married)} will count
single males and all married customers. Keep in
mind that '*' is the AND operator and '+' is the
OR operator.
{=SUM(Male*IF(Married,0,1))} uses an IF() rather
than NOT() to count all single males.
{=SUM(Male*IF(hasBalance,1,0))} counts all males
that have a balance.
{=SUM(Male_Acct_Balances)} sums the account balances
of all males, and is an example of simplification
thru deliberate information hiding.
What do you think?
Posted by Aladin Akyurek on March 26, 2001 10:27 AM
Re: Here's a full blown example of this design concept...
Great. Not copyrighted I hope. You just gave me a practice session I can use with the class. Introducing them to arrays and (hidden) boolean values with familiar concepts. If you have more, don't hesitate...
Posted by Mark W. on March 26, 2001 11:28 AM
Not copywrited...
No time to author a book. Feel free to use any of
these concepts. I'm just trying to advance a
thorough understanding of Excel.
Posted by Mark W. on March 26, 2001 11:29 AM
Re: Not copyrighted... : )
Posted by Mark W. on March 27, 2001 5:34 AM
Refinement...
Instead of defining hasBalance as a reference to
cells D2:D6 as above. It makes more since to make
it's values conform to its meaning by having it
refer to =IF(Acct_Balance,1,0). A corresponding
change to the formula that counts males with a
balance should be made as follows:
{=SUM(Male*hasBalance)}
Posted by Aladin Akyurek on March 27, 2001 7:07 AM
Re: Refinement...
Will include that.
BTW, is it just non-sense what I proposed wtr Jason's interpolation problem?
Aladin
Posted by Mark W. on March 27, 2001 10:23 AM
Finding the Single Male with the Largest Balance
{=INDEX('Name',IF(MAX(Male_Acct_Balances*NOT(Married)),MAX((Acct_Balance=MAX(Male_Acct_Balances*NOT(Married)))*ROW(Acct_Balance)-ROW(D1)),#N/A))}
Note: The label, 'Name', is used here since we hadn't defined a corresponding name.
This formula looks "nastier" than it really is! ; )
Posted by Ian on March 27, 2001 10:49 AM
Please Mark! This is a family site. haha
Posted by Mark W. on March 27, 2001 11:29 AM
A solution using Dfunctions exclusively would require...
A 2-row criteria range that included a DMAX function,
and then a DGET function that would return #NUM! if
there were more than 1 single male with the same
balance.
For example, cells G1:H2 could contain {"Gender","Marital Status";1,0}.
Cell I1 could contain "Acct Balance" while the
Dfunction, =DMAX($A$1:$D$6,$D$1,$G$1:$H$2), would
be entered into cell I2.
The qualifying customer name would be returned by
the Dfunction, =DGET($A$1:$D$6,$A$1,$G$1:$I$2) as
long as the was only 1 customer that "fit the bill".
Posted by Mark W. on March 27, 2001 11:32 AM
Okay, no more risque formulas ; )
