Translating an IF formula from Lotus 1-2-3 to Excel

  • Thread starter Thread starter fsufan13
  • Start date Start date
F

fsufan13

My company has always used Lotus 1-2-3 for spreadsheets in the past. I
am now the financial person and I have vowed to change all my files to
Excel. I have an IF formula that translates to #VALUE, when I am
asking it for ------, as the result to an IF statment in the previous
column that answers a ------ value. Can anyone help me? What info do
I need to post? Or would it be easier if I emailed the file?

Thanks for any help you can give me.

Cheri
 
My company has always used Lotus 1-2-3 for spreadsheets in the past. I
am now the financial person and I have vowed to change all my files to
Excel. I have an IF formula that translates to #VALUE, when I am
asking it for ------, as the result to an IF statment in the previous
column that answers a ------ value. Can anyone help me? What info do
I need to post? Or would it be easier if I emailed the file?

Thanks for any help you can give me.

Cheri
Click to expand...

Post the actual formula, along with actual input and desired output.


--ron
 
The first formula is

=IF(J69-E69>0,ROUND(ROUND(J69-E69,0)/2,0)," ------- ") which results
as ------ in cell M69
(This says if par minus inventory >0, then divide by 2 & result is a
number, if <0 then return
------)

The next formula (which results in #VALUE) is

=IF((J69-E69)-M69>0,ROUND((J69-E69),0)-M69," ------- ")

Hope this makes some sense and thanks for your help.

Cheri
 
Sorry, Ron, I didn't see your question. The output for the first cell
(M69) should be ------(blank) if the difference of par level (J69)
minus (E69) actual inventory divided by 2 < 0. This output is correct.
Then N69 should return ------ also. What these columns represent are
2 orders for the week, half on each of 2 days.

Does this make any sense? Thanks for your help.

Cheri
 
fsufan13 wrote...
The first formula is

=IF(J69-E69>0,ROUND(ROUND(J69-E69,0)/2,0)," ------- ") which results
as ------ in cell M69
(This says if par minus inventory >0, then divide by 2 & result is a
number, if <0 then return ------)

The next formula (which results in #VALUE) is

=IF((J69-E69)-M69>0,ROUND((J69-E69),0)-M69," ------- ")

Hope this makes some sense and thanks for your help.
Click to expand...

The problem isn't with the IF function, it's with using M69 as an
arithmetic operand. Lotus 123 treats all strings as numeric zeros in
all calculations, so in 123 the formulas 1+2*"abc" and 1+2*"3" both
evaluate to 1 ( = 1+2*0). Excel, on the other hand, tries to convert
strings to numbers in arithmetic contexts and numbers to strings in
text contexts. The conversion of the string "abc" to a number fails,
which results in #VALUE!. So in Excel, =1+2*"abc" and =1+2*"3" return
#VALUE! and 7, respectively.

You have two choices: use Transition Formula Evaluation (which you can
enable in the Options dialog, Transition tab), change your first
formula to

=IF(J69-E69>0,ROUND(ROUND(J69-E69,0)/2,0),-1E-300)

and apply custom number formatting so that negative values appear as "
------ ", or change your sedond formula to

=IF((J69-E69)-N(M69)>0,ROUND((J69-E69),0)-N(M69)," ------- ")

Wrapping N(M69) will return the numeric value of M69 when it's numeric
(or boolean) and numeric 0 when it's text.
 
Sorry, Ron, I didn't see your question. The output for the first cell
(M69) should be ------(blank) if the difference of par level (J69)
minus (E69) actual inventory divided by 2 < 0. This output is correct.
Then N69 should return ------ also. What these columns represent are
2 orders for the week, half on each of 2 days.

Does this make any sense? Thanks for your help.

Cheri
Click to expand...


N69 is returning a #VALUE! error because one of its inputs, which is supposed
to be numeric, is your string of dashes. In your first formula, the
logical_test: (J69-E69)-M69>0 fails when it tries to subtract M69 from
(J69-E69). Since M69 is a text string ("-----") and since the formula requires
numeric entries, you get the error.

1. Your logical test is the same as J69-E69-M69>0. In this case, the
parentheses will not change Excel's order of evaluation. In your logical_test
formula, the addition and subtraction will be performed before the comparison
anyway.

2. To eliminate the VALUE error, you either have to have a "number" in M69; or
somehow test for it in your other formula
=IF((J69-E69)-M69>0,ROUND((J69-E69),0)-M69," ------- ")

To change the formula in M69, you could do:

=IF(J69-E69>0,ROUND(ROUND(J69-E69,0)/2,0),0)

but then custom format the cell to show the -'s. To custom format:
Format/Cells/Custom/Type: 0;0;*- will fill the cell with -'s if the value is
zero. You could also format a fixed number of -'s for zero; and you can format
the positive and negative numbers to show decimals.

If this is not convenient (perhaps you might normally want to have a zero in
that cell, then altering the other formula to check the contents of M69 before
doing the math:

=IF(ISNUMBER(M69),IF(J69-E69-M69>0,
ROUND((J69-E69),0)-M69," ------- "),"-------")


--ron
 
Back
Top