Need explanation of this IF formula

**clm4700** · 08-05-2013, 03:35 PM

Ron, can you help me again to solve similar formula problems? Please see the attachment. There are 3 formulas that are not working correctly.

(1) Cells W25 and W56 (in yellow highlight) are not summing correctly.
(2) Formula in Column AM (in green highlight) is not in the proper sequence. That is logically the formula is supposed to be if(a1<>"", if(a1>b1*1.25,c1*1.25*d1, a1*d1),0). But I had to switched the sequence to become if(a1>b1*1.25, a1*d1, c1*1.25*d1) in order to make the calculation correctly. This does not make sense to me, despite it worked at the moment. I think something is wrong here.
(3) Formula in Column AM does not work for Cells AM25 and AM56 (in orange highlight) at all. Currently the formula in the cell is a plugged one.

Thank you very much for your help.

**Ron Coderre** · 08-05-2013, 04:01 PM

From what I can see in your posted workbook...The Col_V values are all text. Consequently, when the conditions match and the SUMIF engages, it attempts to sum the text, not the numeric values. The result is zero.
To convert the values to numeric, try this:
• Select the Col_V values (V19:V97)
• Data.Text-to-Columns...Click: Finish

Does that help?

**clm4700** · 08-05-2013, 05:50 PM

Wow, what a simple mistake. I got the file from another person and assumed the data format was the same but it actually changed. Now all the calculations worked correctly. Thank you very much!