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
LUIS FREUNDAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

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

Start your 7-day free trial
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
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.