asked on

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

Ogandos

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

Alan

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

LUIS FREUND

ASKER

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.....

Alan

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

LUIS FREUND

ASKER

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

LUIS FREUND

ASKER

I got rid of the $0.00.....

Alan

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

LUIS FREUND

ASKER

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

ASKER CERTIFIED SOLUTION

Alan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

LUIS FREUND

ASKER

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

LUIS FREUND

ASKER

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.

Alan

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.

LUIS FREUND

ASKER

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

Alan

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.

LUIS FREUND

ASKER

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!

Alan

No problem at all - very happy to help.

Alan.