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!
Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.