Working with negative #s in spreadsheet

[[email protected]]

I'm using AW 5 on OS 9.2 G4 Cube.
I have a spreadsheet set up so that it counts the number of letters in a quotation and the number of letters in another set of words, compares them, and tells me how many letters in the quotation have not been used.

What I'd like is another column where I can copy the value of the negative numbers (from the letters not yet used) - i.e., changing the sign - and add those numbers, to get a total of letters not yet used.

So - IF E1 < 0, copy the absolute value, then a simple formula to add up those values.

Does that make sense? and if I remember that absolute value is irrespective of the sign, math classes were so long ago.....

I'm sure it's a simple formula but I'm missing The Missing Manual and can't figure it out from the regular AW Help section.

Thanks!
Kathleen Marquardt

[Barry]

I'm using AW 5 on OS 9.2 G4 Cube.
I have a spreadsheet set up so that it counts the number of letters in a quotation and the number of letters in another set of words, compares them, and tells me how many letters in the quotation have not been used.

What I'd like is another column where I can copy the value of the negative numbers (from the letters not yet used) - i.e., changing the sign - and add those numbers, to get a total of letters not yet used.

So - IF E1 < 0, copy the absolute value, then a simple formula to add up those values.

Does that make sense? and if I remember that absolute value is irrespective of the sign, math classes were so long ago.....

I'm sure it's a simple formula but I'm missing The Missing Manual and can't figure it out from the regular AW Help section.

Thanks!
Kathleen Marquardt

As I understand what you are doing, you have 26 cells containing the count of each letter in the quotation (let's say A1 contains the number of As, A2 the number of Bs, etc.), another 26 containing the count of each letter in the second piece of text (B1, B2, etc.) and a third set (in column E) containing the difference between the values in columns A and B.

There are two possibilities for the formula in E1 Either will give a zero result if the number of As in the quotation is equal to the number of As in the new text.

=A1-B1

will report an abundance of As (more in the quote than in the new text) as a POSITIVE number, and a shortage of As (fewer in the quote than in the new text) as a NEGATIVE number.

=B1-A1

will do the reverse. An abundance of As (more in the quote than in the new text) as a NEGATIVE number, and a shortage of As (fewer in the quote than in the new text) as a POSITIVE number.

One way to get a total number of the letters not used is use both the above formulas, the first in column E to report ONLY the letters in abundant supply (ie. with a surplus in the quotation), the second in column F to to report only the letters for which there is a deficit (ie, fewer in the quotation).

Summing column E ( =SUM(E1..E26) would then give you a total count of letters not used. Summing column F ( =SUM(F1..F26) would give a total count of letters used in excess of supply.

For column E (place in E1, then Fill down to E26)

=IF(ABS(A1-B1)=(A1-B1),A1-B1,"")


For column F (place in F1, then Fill down to E26)

=IF(ABS(A1-B1)=(A1-B1),A1-B1,"")

Both formulas as written will report only positive and zero values of the differences. If you want zero values reported only in column E, use the following formula in column F

=IF((A1-B1)>0,A1-B1,"")

Regards,
Barry