Archive of Mr Excel Message Board

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.

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?

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...

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

Will include that.
BTW, is it just non-sense what I proposed wtr Jason's interpolation problem?

Aladin

{=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! ; )

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".