• Status: Solved
• Priority: High
• Security: Public
• Views: 48

# Calculating Cost in Excel with formula

Wanted to calculate cost  at L2 and O2 (highlighted in yellow)  The thing is that I have about a 1000 lines of data and wanted a formula to do this automatically.

On the attachment at columns L and O I've calculated it manually what the cost should be with explanation to better define it.

Please let me know if you have questions....thanks!
0
LUIS FREUND
• 8
• 7
1 Solution

Systems Infrastructure EngineerCommented:
You will use for L2 the formula:
=IF(H2>C2,C2*I2,(H2*I2)-F2)

If you copy it and then paste it to the cells below it will adapt to each row
0

ConsultantCommented:
Hi,

I'm not 100% sure what you mean, but does this work (columns L and O)?

Alan.
EE-29092667-C--Users-lfreund-Downlo.xlsx
0

Author Commented:
Very close......On column L I would like the \$0.00 to be blank since I'm calculating Not On Order at L17.  Also on column L if the cost is less than the cost on column F then it should be negative.

Now on Column O I would only need to show the difference between H2 and C2.  If the qty at column H is greater than the qty at column C, then multiple the difference at column I.  For example...586 is greater than 576.  So it would be 10 times \$14.75 = \$147.50 at column O.  If not's greater than it should be blank.....
0

ConsultantCommented:
Hi Luis,

I have updated - see Version 2 attached.

I am not sure what you mean on this point:

Also on column L if the cost is less than the cost on column F then it should be negative.

Which column or calculation do you want to compare with Column F?

Thanks,

Alan.
EE-29092667-C--Users-lfreund-Downlo.xlsx
0

Author Commented:
Thanks Alan....looks great.  Anyway to get rid of the \$0.00 on column O?

The reason I said negative is because I calculate  it at L15 with =SUMIF(L2:L13,"<0"), but essentially at L15 the formula should be if column L is less than column F then that sum at L15
0

Author Commented:
I got rid of the \$0.00.....
0

ConsultantCommented:
Hi Luis,

Column O fixed.

I have put a formula in column L below the others - blue background.  Does that give the correct 'Total Savings', or do we still need to amend L2:L13?

Thanks,

Alan.
EE-29092667-C--Users-lfreund-Downlo.xlsx
0

Author Commented:
Thank you....So I applied your formulas to my sheet and It's not working properly.  See attachment.  The numbers are huge.....crazy.
1

ConsultantCommented:
Hi,

How does this one look?

Alan.
EE-29092667-C--Users-lfreund-Downlo.xlsx
0

Author Commented:
Looks good but there are rows that are more than \$100,000 when it shouldn't be...I highlighted them in blue for example.....see attachment
0

Author Commented:
So I noticed when  column H is an excess of \$100,000 then it makes the cost on column M in the \$100,000 when it shouldn't.
0

ConsultantCommented:
Hi Luis,

Please can you make a note over on the right of how those cells should be calculated.

Just type it in words as unambiguously as possible.

Thanks,

Alan.
0

Author Commented:
On some of the highlighted ones in blue I've added what the value should be next to it....hope this helps.  See attachment....
0

ConsultantCommented:
Hi Luis,

I see you closed this question - is it all okay now, or do you still need assistance?

Happy to continue helping if you still want me to.

Thanks,

Alan.
0

Author Commented:
Alan, With the help I received from you I was able to close the question.  You have been a tremendous help and I thank you very much for your help!  Awesome job!
0

ConsultantCommented:
No problem at all - very happy to help.

Alan.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.