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

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!
C--Users-lfreund-Downloads-COST2.xlsx
0
LUIS FREUND
Asked:
LUIS FREUND
  • 8
  • 7
1 Solution
 
Schnell SolutionsSystems 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
 
AlanConsultantCommented:
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
 
LUIS FREUNDAuthor 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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
AlanConsultantCommented:
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
 
LUIS FREUNDAuthor 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
 
LUIS FREUNDAuthor Commented:
I got rid of the $0.00.....
0
 
AlanConsultantCommented:
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
 
LUIS FREUNDAuthor Commented:
Thank you....So I applied your formulas to my sheet and It's not working properly.  See attachment.  The numbers are huge.....crazy.
C--Users-lfreund-Downloads-COST3.xlsx
1
 
AlanConsultantCommented:
Hi,

How does this one look?

Alan.
EE-29092667-C--Users-lfreund-Downlo.xlsx
0
 
LUIS FREUNDAuthor 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
C--Users-lfreund-Downloads-COST4.xlsx
0
 
LUIS FREUNDAuthor 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
 
AlanConsultantCommented:
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
 
LUIS FREUNDAuthor 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....
C--Users-lfreund-Downloads-COST4.xlsx
0
 
AlanConsultantCommented:
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
 
LUIS FREUNDAuthor 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
 
AlanConsultantCommented:
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.

Join & Write a Comment

Featured Post

Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

  • 8
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now