Forum Discussion
Using a Sum function within a IF function
Hello, I have possibly a simple question. I have developed a =IF that contains a SUM function. when I attempt to copy it down my spreadsheet, the cells in the SUM statement become absolute I would like them to adjust to the new corresponding cells as I copy down. What do I do. Here is the Function
=IF(E65*G65<0,SUM((((ABS(G67*100)*ABS(E67))))+(((100*M67)*K67)+((100*Q67)*O67)+((100*U67*S67))+((100*Y67)*W67))),"ea")
The E65*G65 adjust accordingly during the copy and paste the rest do not.
Thankyou
3 Replies
I guess your formula could be simplified to
=IF(E65*G65<0,100*(ABS(G67*E67)+M67*K67+Q67*O67+U67*S67+Y67*W67),"ea")if copy/paste one row down it will be as
=IF(E66*G66<0,100*(ABS(G68*E68)+M68*K68+Q68*O68+U68*S68+Y68*W68),"ea")if that's what do you mean. Your initial formula with SUM shall return exactly the same result.
Entered your formula in a new workbook and copied it down a few rows. None of the cell references became absolute for me. Perhaps you can upload your workbook to demonstrate the problem.
By the way, you may want to make your formula a bit easier to read and maintain. The SUM statement is not needed and it will reduce the number of brackets. I think this one will achieve the same result (with row 1 as the base in stead of 65 in your example);
=IF(E1*G1<0,ABS(G3*100)*ABS(E3)+100*M3*K3+100*Q3*O3+100*U3*S3+100*Y3*W3,"ea")
