Excel spreadsheet

[Telp]

I'm trying to figure out the formula for a cell that calls for a Y (yes) or N (no) entry (sheet 2) so that it ports over to the overview sheet (sheet 1) as a numeric value .

In other words, if a Y is placed in cell H3 on sheet #2, how can I get it to show up on sheet #1, cell E8, as a numeric value.

ex. 7 people answered yes to the first question on sheet #2...

Thanks!

Telp

 

[mkrishnan]

If it's just one cell, you can use a straight if for this purpose...something like

if(Sheet2!H3="Y", 1, 0)

(syntax might be slightly off)

If there is a range, countif is probably a better option.

countif(Sheet2!H3:H40, "Y")

would produce the number of Y's in that range.

 

[Telp]

If it's just one cell, you can use a straight if for this purpose...something like

if(Sheet2!H3="Y", 1, 0)

(syntax might be slightly off)

If there is a range, countif is probably a better option.

countif(Sheet2!H3:H40, "Y")

would produce the number of Y's in that range.

Perfect! That is exactly what I needed. Thank you very much!


-Telp

 

[Slumbercub]

Just a little tip that may add a bit of elegance to your spreadsheet. Instead of entering 'Y' you may want to use a checkbox (inspector, cell inspector, drop down box). If it is empty it is No if ticked it is Yes. Just point the formula condition of the IF function to the cell with the checkbox and then use 1 for [if-true] and 0 for [if-false].

 

[HansHans]

Just a little tip that may add a bit of elegance to your spreadsheet. Instead of entering 'Y' you may want to use a checkbox (inspector, cell inspector, drop down box). If it is empty it is No if ticked it is Yes. Just point the formula condition of the IF function to the cell with the checkbox and then use 1 for [if-true] and 0 for [if-false].

Hi Slumbercup,

very interesting comment. Just one question: how do I bring up the inspector you mentioned in Excel? I can't find it anywhere.

Thanks for your help.

Hans

 

[Slumbercub]

No idea I am afraid, the way I described it was using Numbers but obviously Excel has the same functionality.

 

[HansHans]

No idea I am afraid, the way I described it was using Numbers but obviously Excel has the same functionality.

No, it hasn't, I'm afraid. The Windows Excel has a "design mode" that let's you create command buttons, listboxes, dropdown boxes, checkboxes etc. and I can't find that in Excel for Mac.

 

[HansHans]

No idea I am afraid, the way I described it was using Numbers but obviously Excel has the same functionality.

Just wanted to say: I found it at last. A post on the "micosoft.public.mac.office.excel" newsgroup hinted me to it.

The controls can befound here: On the menu click "view-->toolbars-->forms".