Simple question. Calculating Z scores

dneunabe · Mar 30th, 2004, 07:09 AM

I am trying to assign values to two cells (e.g. mean and standard deviation) based on values in three others cells, containing age, education, and gender. E.G. the value for the mean in cell B12 is assigned based on the age range of a person, their educational status (also a range), and their gender. Hence, the value in a cell is conditional based on three conditions. I have a 3 way table, so it is a matter of the formula "telling" which number belongs in my formula for calculating standard scores based on means and SDs--e.g. simple z-score calculations.

as an aside, does the Excel for Scientists from Mr. Excel solve this type of problem?

Thanks much!
dan neunaber

Zack Barresse · Mar 30th, 2004, 07:16 AM

hiya Dan! how are ya.

care to post your data with Colo's HTML Maker? along with a graphic representation of your data (scaled if you must), an example of your current formula/s would be most helpful.

fairwinds · Mar 30th, 2004, 07:37 AM

Hi,

Is this what you want ?

Both formulas are array formulas and must be entered with Ctrl + Shift + Enter.

******** ******************** ************************************************************************>

Microsoft Excel - Book1

___Running: xl2000 : OS = Windows Windows 2000

(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout

=

A

B

C

D

E

F

G

1

Age

Education

Gender

Value

*

Min*age

22

2

21

A

M

100

*

Max*age

24

3

22

A

M

102

*

Education

A

4

23

A

M

104

*

Gender

M

5

24

A

M

106

*

6

25

B

M

108

*

Average

104

7

26

B

F

110

*

Stdev

2

8

27

B

F

112

*

9

28

A

F

114

*

10

29

A

F

116

*

Sheet1

*

[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

dneunabe · Mar 30th, 2004, 02:40 PM

Thanks,
I think I'll need to go figure out how to use the HTML Maker software to which you refer.

In the interim, what I want is for the machine to pick a value using the following logic:

If A = X and if B = Y, then C = (a specific cell--and of course its value).
Where C is in a simple table of values stratified according to age and education.

fairwinds · Mar 30th, 2004, 02:56 PM

Sorry, don't get it, you had better post your sheet. Link to HTML maker is on bottom of this page.

Zack Barresse · Mar 30th, 2004, 03:57 PM

well if you have your values are already going to be there (via formulation), and you already have a matrix set-up (sounds like you do), it sounds like an INDEX/MATCH function is needed here.

i could be wrong, but more data will help. (your lucky day, fairwinds is probably the king of the index/match

)

dneunabe · Apr 2nd, 2004, 04:57 AM

Sorry to clog up the bulletin board... I tried to download the HTML maker, and got a message in computerese "Error Number=91. Error Discrp=Object Variable or...block variable not set." Does anyone know what this means, what to do about it, and how to get the HTML maker up and running? Thanks,
Dan

PaddyD · Apr 2nd, 2004, 05:05 AM

Dan,

plenty of threads in the 'about the board' forum on html maker problems - do a search & you shuold find something useful.

Mar 30th, 2004, 07:09 AM	#1
dneunabe Join Date: Feb 2004 Posts: 12	Simple question. Calculating Z scores I am trying to assign values to two cells (e.g. mean and standard deviation) based on values in three others cells, containing age, education, and gender. E.G. the value for the mean in cell B12 is assigned based on the age range of a person, their educational status (also a range), and their gender. Hence, the value in a cell is conditional based on three conditions. I have a 3 way table, so it is a matter of the formula "telling" which number belongs in my formula for calculating standard scores based on means and SDs--e.g. simple z-score calculations. as an aside, does the Excel for Scientists from Mr. Excel solve this type of problem? Thanks much! dan neunaber __________________ Dan

Mar 30th, 2004, 07:16 AM	#2
Zack Barresse MrExcel MVP Join Date: Dec 2003 Location: Oregon, USA Posts: 9,624	hiya Dan! how are ya. care to post your data with Colo's HTML Maker? along with a graphic representation of your data (scaled if you must), an example of your current formula/s would be most helpful. __________________ Regards, Zack Barresse All Excel Functions (If you would like comments in any code, please say so.)

Mar 30th, 2004, 02:40 PM	#4
dneunabe Join Date: Feb 2004 Posts: 12	Re: Simple question. Calculating Z scores from Dan Thanks, I think I'll need to go figure out how to use the HTML Maker software to which you refer. In the interim, what I want is for the machine to pick a value using the following logic: If A = X and if B = Y, then C = (a specific cell--and of course its value). Where C is in a simple table of values stratified according to age and education. __________________ Dan

Mar 30th, 2004, 02:56 PM	#5
fairwinds MrExcel MVP Join Date: May 2003 Posts: 8,456	Re: Simple question. Calculating Z scores Sorry, don't get it, you had better post your sheet. Link to HTML maker is on bottom of this page. __________________ "Fair Winds and Following Seas"

Mar 30th, 2004, 03:57 PM	#6
Zack Barresse MrExcel MVP Join Date: Dec 2003 Location: Oregon, USA Posts: 9,624	well if you have your values are already going to be there (via formulation), and you already have a matrix set-up (sounds like you do), it sounds like an INDEX/MATCH function is needed here. i could be wrong, but more data will help. (your lucky day, fairwinds is probably the king of the index/match ) __________________ Regards, Zack Barresse All Excel Functions (If you would like comments in any code, please say so.)

Apr 2nd, 2004, 04:57 AM	#7
dneunabe Join Date: Feb 2004 Posts: 12	Re: Simple question. Calculating Z scores Sorry to clog up the bulletin board... I tried to download the HTML maker, and got a message in computerese "Error Number=91. Error Discrp=Object Variable or...block variable not set." Does anyone know what this means, what to do about it, and how to get the HTML maker up and running? Thanks, Dan __________________ Dan

Apr 2nd, 2004, 05:05 AM	#8
PaddyD MrExcel MVP Join Date: May 2002 Posts: 12,561	Re: Simple question. Calculating Z scores Dan, plenty of threads in the 'about the board' forum on html maker problems - do a search & you shuold find something useful.

Thread Tools
Show Printable Version Email this Page
Display Modes
Switch to Linear Mode Hybrid Mode Switch to Threaded Mode