Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    CT
    Posts
    361
    Thanks
    0
    Thanked 1 Time in 1 Post
    Can anyone see any inherent error in this formula? The formula resides in the cells in column in "L". It worked fine as shown in the earlier version (below), but after making changes today as shown here I got some weird results.


    Weird result version:
    =IF(OR(C16="",K16=""),"",IF(OR(J16="NO CHARGE",J16="N/C"),"",IF(AND(J16=0,K16<>".00"),C16*K16,IF(K16<>". 00", C16*(ROUND(J16,0)+K16),C16*(ROUND(J16,0))))))



    Previous to today's changes I ran the formula this way with no problem:

    =IF(OR(J16="NO CHARGE",J16="N/C"),0,IF(C16*J16=0," ", IF(K16<>".00", C16*(ROUND(J16,0)+K16),C16*(ROUND(J16,0)))))



    Right after creating the changes and copying the revised formula to all the cells in column "L" I got some blatant math errors. If I put a value in J16 first, and then a value in C16 (C16*J16), I would get the right answer. However, if entered a number in C16 first.....which is how it would be done in daily use...... I would get the math error. I don't mean an "error message".....I mean a "math error" such as 2*2=5. Doesn't seem possible but it was happening.

    This happened for 3 or 4 minutes of experimenting to find out what was wrong and then the problem just went away on its own......I didn't change anything in the formula. Since this formula repeats in every cell in column "L" from L15 to L38 I would have had to copy any unintended change all the way down the column....which I didn't do. Very weird. Now even though it is working right I don't trust the formula.

    My only hope is I've done something stupid that Excel might be reading different ways on different occasions for some reason ???


    It might help to know that:

    C16 is "quantity of items"

    J16 is cost in dollars (entered as dollars and cents but rounds up or down)

    K16 is cost in cents (this column is formatted as text. The default ".00" displays if there is a dollar value in J16. It is sometimes overwritten manually with a cents amount such as ".50". Even though it is formatted as text it multiplies out properly into column "L".)

    Thanks,
    BH

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    As far as I can tell, you can simplify the formula to

    =IF(OR(C16="",K16="",J16="NO CHARGE",J16="N/C"),"",C16*(ROUND(J16,0)+K16))

    I'd need to see (a stripped down copy of) the workbook to know why it misbehaves.

  3. #3
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    CT
    Posts
    361
    Thanks
    0
    Thanked 1 Time in 1 Post
    Thanks Hans. I think I'm just going to forget about this change and leave the sheets the way they were before. It was a somewhat picky detail that really never comes into play anyway.

    BH


    [quote name='HansV' post='781204' date='23-Jun-2009 13:52']As far as I can tell, you can simplify the formula to

    =IF(OR(C16="",K16="",J16="NO CHARGE",J16="N/C"),"",C16*(ROUND(J16,0)+K16))

    I'd need to see (a stripped down copy of) the workbook to know why it misbehaves.[/quote]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •