If statements and multiplying #'s

Guest · Dec 4, 2003

Is there a limit on character spacing in formulas? I have a this statement =IF(OR(B6="Dahlmeier",B6="NCPHS",B6="TCB",B6="AES",B6="BCB",B6="Heritage Partners",B6="Mang Vet",B6="Farmers In",B6="Cascade",B6="Ben Toilet",B6="BHH",B6="Hobbie",B6="Evans Appraisal",B6="Azevedo Chiro",B6="CHIP",B6="Quadco",B6="Chico Med",B6="Gallaghers",B6="ServPro",B6="Penny Wallace",B6="AA Bus Sys",B6="Wayne Harder",B6="Mang Medical",B6="David Howard",B6="Telephone Man",B6="City of Chico MIS",B6="Harris Sanford",B6="Wagner Ins"),"Old",IF(B6="","","New"))
I would like to add more names to the "Old" list, but am unable to.

Also, on another spreadsheet I am calculating costs. Example: First column is Quantity, Second is My Cost, Third is the Total Cost to Me, Fourth is the price I'm selling it for, and Fifth is the total price.
In the first column I have a quantity of 5. My cost is 5.59 each, so in the third column I have this formula =A2*B2 which returns the number 27.95. The "Sale Price" formula is =B2/.75 which in this case returns the number 7.45333333. In the last column I have this formula =A2*D2, which is 5*7.45333333. That returns 37.26666667. I format these two cells to only have 2 digits after the decimal, and the number that results is 7.45 and 37.27. Well, if you multiply 5*7.45, it's 37.25. How can I get Excel to show this?
Please e-mail with any help! Thanks.

Peo Sjoblom · Dec 4, 2003

If you check Excel's help you would see that OR can hold 30 conditions, you
could easily
work around that if you for instance put all the names in a range with a
name per cell, assume you put them in
H2:H50, then use this formula

=IF(B6="","",IF(COUNTIF(H2:H50,B6),"Old","New"))

--

Regards,

Peo Sjoblom

Angela said:
Is there a limit on character spacing in formulas? I have a this statement

=IF(OR(B6="Dahlmeier",B6="NCPHS",B6="TCB",B6="AES",B6="BCB",B6="Heritage
Partners",B6="Mang Vet",B6="Farmers In",B6="Cascade",B6="Ben
Toilet",B6="BHH",B6="Hobbie",B6="Evans Appraisal",B6="Azevedo
Chiro",B6="CHIP",B6="Quadco",B6="Chico
Med",B6="Gallaghers",B6="ServPro",B6="Penny Wallace",B6="AA Bus
Sys",B6="Wayne Harder",B6="Mang Medical",B6="David Howard",B6="Telephone
Man",B6="City of Chico MIS",B6="Harris Sanford",B6="Wagner
Ins"),"Old",IF(B6="","","New"))

I would like to add more names to the "Old" list, but am unable to.

Also, on another spreadsheet I am calculating costs. Example: First column

is Quantity, Second is My Cost, Third is the Total Cost to Me, Fourth is the
price I'm selling it for, and Fifth is the total price.

In the first column I have a quantity of 5. My cost is 5.59 each, so in

the third column I have this formula =A2*B2 which returns the number 27.95.
The "Sale Price" formula is =B2/.75 which in this case returns the number
7.45333333. In the last column I have this formula =A2*D2, which is
5*7.45333333. That returns 37.26666667. I format these two cells to only
have 2 digits after the decimal, and the number that results is 7.45 and
37.27. Well, if you multiply 5*7.45, it's 37.25. How can I get Excel to show
this?

Peo Sjoblom · Dec 4, 2003

If you check Excel's help you would see that OR can hold 30 conditions, you
could easily
work around that if you for instance put all the names in a range with a
name per cell, assume you put them in
H2:H50, then use this formula

=IF(B6="","",IF(COUNTIF(H2:H50,B6),"Old","New"))

For your second question, round the formulas to 2 decimals

=ROUND(B2/75,2)

and so on. Formatting has nothing to do with the real values,
if you need that you can select precision as displayed under
tools>options>calculations
but it is better to use round

--

Regards,

Peo Sjoblom