Frequency question

[Harvey Waxman]

I have this bins-array formula in S1:S7: {=FREQUENCY(S11:S4000,R1:R6)}

All the data in S 11:S4000 is formatted as a percentage.

How is it possible for the total of the returned values to be less than the
total number of entries in S11:S4000? The total of the returned values is 3721
but there are 3990 entries.


R S
1 0% 793
2 5% 574
3 13% 776
4 25% 729
5 100% 777
6 200% 34
7 38

Total 3721



--
Harvey Products makers of Dinghy Dogs
The Boater's Best Friend
http://www.dinghydogs.com
Ladies' handbags and accessories
http://www.deducktibles.com
Remove thefrown to email me

 

[Michael R Middleton]

Harvey Waxman -

I have this bins-array formula in S1:S7: {=FREQUENCY(S11:S4000,R1:R6)}

All the data in S 11:S4000 is formatted as a percentage.

How is it possible for the total of the returned values to be less than

the total number of entries in S11:S4000? The total of the returned values
is 3721 but there are 3990 entries. ...

Maybe some of your cells are not really numbers.

Excel's built-in Help says "FREQUENCY ignores blank cells and text."

- Mike Middleton

www.mikemiddleton.com

 

[Michael R Middleton]

Harvey Waxman -

In case some of your data may actually be text instead of numbers, here's a
quick way to fix the data described by JE McGimpsey in a recent message:

"You can manually coerce the Text Values:

Select an empty cell. Copy the cell. Select the cells with your numbers.
Choose Edit/Paste Special, selecting the Values and Add radio buttons."

- Mike Middleton

www.mikemiddleton.com

 

[Harvey Waxman]

Harvey Waxman -

In case some of your data may actually be text instead of numbers, here's a
quick way to fix the data described by JE McGimpsey in a recent message:

"You can manually coerce the Text Values:

Select an empty cell. Copy the cell. Select the cells with your numbers.
Choose Edit/Paste Special, selecting the Values and Add radio buttons."

- Mike Middleton

www.mikemiddleton.com

Thank you very much. That corrected the problem.

Now what does that do exactly? Is there an explanation of this, since it
doesn't seem to be logical on its face.

Before that, I used the format pane to change the data to Percent which did
nothing of course.



--
Harvey Products makers of Dinghy Dogs
The Boater's Best Friend
http://www.dinghydogs.com
Ladies' handbags and accessories
http://www.deducktibles.com
Remove thefrown to email me

 

[JE McGimpsey]

Now what does that do exactly? Is there an explanation of this, since it
doesn't seem to be logical on its face.

It's not necessarily logical, but it's consistent. In performing a Paste
Special/Add, XL attempts to interpret both the original value and the
pasted value as numbers, if possible, just as it does with manual
entries. The result, if both of the values can be interpreted as
numeric, is a number, not text. A blank cell is interpreted as zero, so
adding zero to any "coerce-able" numbers returns the original numbers as
numeric values.

 

[Harvey Waxman]

A blank cell is interpreted as zero, so
adding zero to any "coerce-able" numbers returns the original numbers as
numeric values.

Thanks. It makes sense now. How come simply formatting as numbers didn't work
but this does?

--
Harvey Products makers of Dinghy Dogs
The Boater's Best Friend
http://www.dinghydogs.com
Ladies' handbags and accessories
http://www.deducktibles.com
Remove thefrown to email me

 

[JE McGimpsey]

Thanks. It makes sense now. How come simply formatting as numbers
didn't work but this does?

Because changing the number format only works on numbers. Your text
values weren't numbers.

 

[Harvey Waxman]

Thanks. It makes sense now. How come simply formatting as numbers
didn't work but this does?

Because changing the number format only works on numbers. Your text
values weren't numbers.[/QUOTE]

Sorry for being so slow. They data was the result of a formula acting on
numeric data to produce percentages. How can they be anything but numbers in
the first place?

If I format a group of data as numbers, does this not change them from text to
numbers? Very confusing and the help file isn't helpful here.

For example: =LOOKUP(1,A13:A43) returns a value of 1
Where the data in A13:A43 is formatted as number. I also used the paste
special value add scenario on the data but the 1 remains.
I'm doing something wrong but can't see it.

1
1
1
1
4
13
13
1
13
13
13
13
13
13
1
1
13
1
1
13
1
1
1
1
1
1
79
13
13
13
13


--
Harvey Products makers of Dinghy Dogs
The Boater's Best Friend
http://www.dinghydogs.com
Ladies' handbags and accessories
http://www.deducktibles.com
Remove thefrown to email me

 

[JE McGimpsey]

Sorry for being so slow. They data was the result of a formula acting on
numeric data to produce percentages. How can they be anything but numbers in
the first place?

If I format a group of data as numbers, does this not change them from text
to
numbers? Very confusing and the help file isn't helpful here.

For example: =LOOKUP(1,A13:A43) returns a value of 1
Where the data in A13:A43 is formatted as number. I also used the paste
special value add scenario on the data but the 1 remains.
I'm doing something wrong but can't see it.

I guess I'm not sure what you're trying to illustrate. If you have
results of a formula, the value will be either numeric or text,
depending on the output type of the formula. For instance:

A1: 5
B1: 10
C1: =A1/B1

will return numeric 0.5, which you can format as 0.0% to give 50.0%.
OTOH,

C1: =TEXT(A1/B1,"0.0%")

will return a Text 50.0% that will be completely unresponsive to number
format.

Your Lookup(1,rng) example will find only numeric 1, not text.

Once a value is entered as Text, however (often the result when data
from a web page is pasted in, for example), it's Text until and unless
it's coerced into a number, no matter what the format.

 

[Harvey Waxman]

Once a value is entered as Text, however (often the result when data
from a web page is pasted in, for example), it's Text until and unless
it's coerced into a number, no matter what the format.

I think this is the key to the problems I was having. It is a bit of a bug
when a menu choice doesn't do what it is supposed to. Has this been fixed in
2004?

And thanks for the help.

Harvey


--
Harvey Products makers of Dinghy Dogs
The Boater's Best Friend
http://www.dinghydogs.com
Ladies' handbags and accessories
http://www.deducktibles.com
Remove thefrown to email me

 

[JE McGimpsey]

I think this is the key to the problems I was having. It is a bit of a bug
when a menu choice doesn't do what it is supposed to. Has this been fixed in
2004?

It's not a bug and it hasn't been changed.

Format and Data Type are separate things. The menu, as it's supposed to,
changes the data *format* of a numeric value to Text (i.e., left aligns
it, or whatever you've modified the Text style to), or to another number
format, but it doesn't change the data *type* to Text. While it
certainly can be confusing, it works the way it's designed to.

If you think that it's poor design, send feedback to MacBU via XL v.X's
Help/Feedback on Excel... menu item, along with a message to

(e-mail address removed)

Since the behavior is the same in WinXL, I don't expect it has any
chance of changing unless the WinXL version changes, too...

 

[Harvey Waxman]

It's not a bug and it hasn't been changed.

Format and Data Type are separate things.

I hate to beat a dead horse (or any horse for that matter) but it does seem
strange that the results of a calculation could be treated as text and might
need to be coerced into being a number.

I don't think I saw any reference to what one should do when an error such as
#DIV/0! appears and all the entries certainly look like numbers, are the result
of calculations and have been formatted as numbers. There should be an
explanation in the help file of what might be happening.
--
Harvey Products makers of Dinghy Dogs
The Boater's Best Friend
http://www.dinghydogs.com
Ladies' handbags and accessories
http://www.deducktibles.com
Remove thefrown to email me

 

[JE McGimpsey]

I hate to beat a dead horse (or any horse for that matter) but it does seem
strange that the results of a calculation could be treated as text and might
need to be coerced into being a number.

Since you didn't post your formula(e), I'm not sure what's going on
there - a numeric formula will only return numeric data. You don't have
anything like

=IF(B1<>0,A1/B1,"")

do you? If B1 = 0, the Text null string is returned.

Likewise if you had

I don't think I saw any reference to what one should do when an error
such as #DIV/0! appears and all the entries certainly look like
numbers, are the result of calculations and have been formatted as
numbers. There should be an explanation in the help file of what
might be happening.

XL04 has "smart buttons" that pop up when there's an error (or, often, a
potential error), that has links to the help topics.

For instance, a smart button pops up when you have numbers stored as
text.

 

[Harvey Waxman]

I don't think I saw any reference to what one should do when an error
such as #DIV/0! appears and all the entries certainly look like
numbers, are the result of calculations and have been formatted as
numbers. There should be an explanation in the help file of what
might be happening.

XL04 has "smart buttons" that pop up when there's an error (or, often, a
potential error), that has links to the help topics.[/QUOTE]


That's an improvement.


--
Harvey Products makers of Dinghy Dogs
The Boater's Best Friend
http://www.dinghydogs.com
Ladies' handbags and accessories
http://www.deducktibles.com
Remove thefrown to email me