text and numbers same cell and formulas still work (like lotus)

[Guest]

I am converting a Lotus 1-2-3 spreadsheet to Excel 2003 (SP3). Lotus allows
for text or numbers to be entered into a cell that is referenced in a formula
that calculated a price. If text is entered in the cell it looks as if Lotus
ignores it and the calculation is not executed.

If I try this with Excel, it executes the text and renders an error. Is
there a way to duplicate what Lotus is doing?

 

[Guest]

Hi rmoore

I do not know in which cells you have the problem, but for the sake of this
excercise, let's say Col A may contain text or numbers, and you want to
multiply with Col B, to get a result in Col C.

In say C2 insert the following formula:

=IF(ISTEXT(A1),"",B1*A1)

Adjust to suit your locations, and copy down

 

[Harlan Grove]

rmoore wrote...

I am converting a Lotus 1-2-3 spreadsheet to Excel 2003 (SP3). Lotus allows
for text or numbers to be entered into a cell that is referenced in a formula
that calculated a price. If text is entered in the cell it looks as if Lotus
ignores it and the calculation is not executed.

If I try this with Excel, it executes the text and renders an error. Is
there a way to duplicate what Lotus is doing?

123 doesn't ignore it, it treats *ALL* text as zero in numeric
calculations. The simplest way to do that in Excel is to wrap
references to cells that could contain text or numbers inside N(.)
calls. so replace formulas like

=B2*C5

with

=N(B2)*N(C5)

 

[Guest]

Kassie,

Thank you for your help. The solution fits perfectly.

Randy

 

[Guest]

Harlan,

Thanks for you help. This solution will be eaiser for me to put in place.

Randy