Solved

Help with Excel Spreadsheet and formulas

Posted on 2014-01-28
43
174 Views
Last Modified: 2014-06-11
I am looking for a solution to a problem I am having with this document.

Explanation of the document-
The document has 4 pages –
MRPExport – This is where the purchasing recommendations are pasted
MRP Recommendations with lead – (Main Sheet) - This combines information form MRPExport and Items Sheet and runs some Calculations
Items – This is a list of items from the system which contains some extra information than MRPExport
Exchange Rates – this is used to convert all Currencies into GBP

As you can see I have two totals in AQ2 and AR2, the problem is that these totals are not correct, AQ2 is calculated by -  Actual Quantity Column * Cost Column. AR2 is calculated by – Adjusted Quantity Column * Cost Column
These totals are designed to show the total costs of all the purchase orders recommended by the system.

The Problem –
As well as recommending new purchase order (new P/O), this document also recommends to cancel Purchase orders (Cancel P/O) unfortunately the system does not record the prices for the cancellation recommendations only the ‘new P/O recommendations’. As a result the total costs are not accurate.
NOTE - When there is a ‘Cancel P/O’ There is not always a ‘new P/O’ Recommendation.

Solution –
I need the cost column to display the cost of the item in minus figures for lines that contain the word Cancel in the Message column, AND have the same item on a PO Recommendation so that they will be subtracted from the total.

Summary –
The Formula needs to do this -
Put a Minus figure of the Cost of the Product Code in column ‘Cost’ Under these conditions –
There is a Cancel Recommendation in the Message Column AND a New P/O Recommendation in the message column for the same Product Code. If there is no New P/O recommendation it should put a 0 in the Cost Column. It should also show a positive Cost for all other items.
Calculations-Document.xlsx
0
Comment
Question by:broadcastwarehouse
  • 22
  • 21
43 Comments
 
LVL 23

Expert Comment

by:NBVC
Comment Utility
Try:

=IF(LEFT(D2,10)="Cancel PO",IF(COUNTIFS($D$2:$D$10,"New P/O",$E$2:$E$10,E2)>1,-SUMIFS(MRPExport!L:L,MRPExport!D:D,D2,MRPExport!E:E,E2)),SUMIFS(MRPExport!L:L,MRPExport!D:D,D2,MRPExport!E:E,E2))

Open in new window


copied down
0
 

Author Comment

by:broadcastwarehouse
Comment Utility
Many thanks. Which column does this need to go in?
0
 
LVL 23

Expert Comment

by:NBVC
Comment Utility
Starting in O2 of "MRP Recommends with Lead" sheet, copied down
0
 

Author Comment

by:broadcastwarehouse
Comment Utility
This doesn't work! I get an error message - please see attached
Error.docx
0
 

Author Comment

by:broadcastwarehouse
Comment Utility
This doesn't work! I get an error message - please see attached
0
 
LVL 23

Expert Comment

by:NBVC
Comment Utility
All you need to is hit Enter.  You probably didn't copy all the brackets in my formula so Excel is letting you know...
0
 

Author Comment

by:broadcastwarehouse
Comment Utility
Yes I had missed off a bracket at the end when copying. I have corrected this so I no longer get a warning.
However I still don't see any change. The lines that have a 'Cancel' recommendation (and have a New P/O recommendation) should display a Minus figure of the cost, but they still show 0 as before. see image below after your formula has been entered.

Formula not working
0
 
LVL 23

Expert Comment

by:NBVC
Comment Utility
Which ones?

Item 1 is the only duplicated item and the corresponding cost in columm L on the other sheet is 0.

Can you please indicate the expected results for this sample along with explanation of where it came from and why.
0
 

Author Comment

by:broadcastwarehouse
Comment Utility
OK Let me try to explain it more clearly, I need the totals columns AN and AO to display positive costs for all New P/O Lines, (it already does this)
AND
Negative Costs for all Cancel P/O Lines (It currently does not do this)

The Cancel Lines do not show a cost on the MRPexport page, so they will need to draw the cost from the same cell that the new P/O gets the cost from

Does that make sense? Sorry it is hard to explain. I want Column AN and AO to look like this -
Ideal AN AND AO Columns
0
 
LVL 23

Accepted Solution

by:
NBVC earned 500 total points
Comment Utility
Ok, I got this for column O (Costs)

=SUMIF(MRPExport!E:E,E2,MRPExport!L:L)*IF(LEFT(D2,10)="Cancel P/O",-1,1)

copied down.

It hasn't adjusted AN and AO because they are dependent on info in other columns too, like E, F, and M and N...  and I am not sure of your logic in getting all that info...
0
 

Author Comment

by:broadcastwarehouse
Comment Utility
I have managed to get these columns to display minus figures on Cancel lines, but it does it for all lines that have Cancel.
I only want it to show a Minus when there is a New P/O Line for the same item.
Attached is the file as it is now.

Calculations-Document---Bens-edi.xlsx
0
 
LVL 23

Expert Comment

by:NBVC
Comment Utility
Try, in AM2:

=IF(LEFT(MRPExport!D2,6)="Cancel",IF(COUNTIFS($D$2:$D$10,"New P/O",$E$2:$E$10,E2)>0,-J2*P2,J2*P2),J2*P2)

copied down.
0
 

Author Comment

by:broadcastwarehouse
Comment Utility
Thanks a lot, I assume you meant AN2 not AM2...
This still wasn't quite Right as it was returning a Positive value of the Cost for items that were only showing a cancel and not both a cancel and New PO, but I adjusted it to this -

=IF(LEFT(MRPExport!D10,6)="Cancel",IF(COUNTIFS($D$2:$D$10,"New P/O",$E$2:$E$10,E10)>0,-J10*P10,0),J10*P10)

And now it works just right! :) Thanskyou so much for your help and your patience with me!
Kind Regards, Ben.
0
 
LVL 23

Expert Comment

by:NBVC
Comment Utility
Ok. You're welcome.  Glad you got to the desired end result finally.
0
 

Author Comment

by:broadcastwarehouse
Comment Utility
After reviewing this it is still not correct.

The problem now is that the minus figures for Item1 are greater than the new P/O cost of Item1 so the total shows a minus figure.

What I actually need it to is the following -

If there is a Cancel P/O and New P/O for any item, add up the total cost of all the Cancel P/O for that item, and minus that from the Cost of the New P/O THEN If the answer is less than zero show Zero in column AM, if it is more than zero, show that cost in AM
0
 
LVL 23

Expert Comment

by:NBVC
Comment Utility
What happens to the New P/O value for that item?
0
 

Author Comment

by:broadcastwarehouse
Comment Utility
The total value (Cost*actual quantity) for that item, will be reduced by deducting the total of all the Cancel P/O costs*actual Quantities for that item.

So in the case of Item1

if it recommends 1000pcs on the new P/O line, but the cancellations add up to 900, it will only show a cost of 100*34.8 in the Column AM
0
 
LVL 23

Expert Comment

by:NBVC
Comment Utility
Let's give this one a try:

=IF(D2="New P/O",ABS(SUMPRODUCT(--($D$2:$D$10="New P/O"),--($E$2:$E$10=E2),$J$2:$J$10,$P$2:$P$10)-SUMPRODUCT(--(LEFT($D$2:$D$10,6)="Cancel"),--($E$2:$E$10=E2),$J$2:$J$10,$P$2:$P$10)),IF(SUMPRODUCT(--($D$2:$D$10="New P/O"),--($E$2:$E$10=E2),$J$2:$J$10,$P$2:$P$10)-SUMPRODUCT(--(LEFT($D$2:$D$10,6)="Cancel"),--($E$2:$E$10=E2),$J$2:$J$10,$P$2:$P$10)<0,0,J2*P2))

Open in new window

0
 

Author Comment

by:broadcastwarehouse
Comment Utility
Sorry but I pasted this in AM2 and copied down.
I got a huge number - 45824900

I have a P/o recommendation for 1000pcs at 34.8 = £34800.00
I have cancellations for ITEM1 that total = £32155.20

So I am expecting AM2 to show £34800.00 - £32155.20 = £2644.80
0
 
LVL 23

Expert Comment

by:NBVC
Comment Utility
In the sample workbook you provided, I got close to your number.. (I don't know your exact numbers for the cancelled pos so I adusted the numbers in column J to get close to you) but the result indicates the numbers work....
Calculations-Document---Bens-edi.xlsx
0
 

Author Comment

by:broadcastwarehouse
Comment Utility
Thanks, Sorry, don't know what I had done on the sheet I was using!
This is looking good, the reason it is close and not Exactly the same number, is because the Cost values show 38 instead of 34.8.
This is taking the cost from the supplier on the items sheet.
If it took it form the MRPExport sheet instead, It would work perfectly.
Can you help me to figure this last bit out please?
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 23

Expert Comment

by:NBVC
Comment Utility
From column L?  I see 0's for those canceled PO's, is that what should go in the Cost column of MRP Recommends with Lead sheet?
0
 

Author Comment

by:broadcastwarehouse
Comment Utility
It depends how your calculation works on AN?
As long as it does the deduction of the cancel Orders from the new PO correctly. and as you say it is close but not quite right. I think it must be that these show 38 rather 34.8??
0
 

Author Comment

by:broadcastwarehouse
Comment Utility
it would need to read from the new P/O Item1 cost if it did need to display it.
0
 
LVL 23

Expert Comment

by:NBVC
Comment Utility
Try:

=SUMIFS(MRPExport!L:L,MRPExport!D:D,"New P/O",MRPExport!E:E,E2)
0
 

Author Comment

by:broadcastwarehouse
Comment Utility
Hi this is good, the attached is working right except for one thing.
if the Actual Quantity for a new P/O item is lower than the total quantity of all the Cancellations added together, the new P/O Line should return a zero in the AL column.

At the moment it is still showing the actual QTY*cost.

Calculations-Document---Bens-edi.xlsx

Thanks, Ben.
0
 
LVL 23

Expert Comment

by:NBVC
Comment Utility
.... becoming confusing ...

try:

=IF(AND(D2="New P/O",(SUMPRODUCT(--($D$2:$D$10="New P/O"),--($E$2:$E$10=E2),$I$2:$I$10,$N$2:$N$10)-SUMPRODUCT(--(LEFT($D$2:$D$10,6)="Cancel"),--($E$2:$E$10=E2),$I$2:$I$10,$N$2:$N$10))<0),0,IF(SUMPRODUCT(--($D$2:$D$10="New P/O"),--($E$2:$E$10=E2),$I$2:$I$10,$N$2:$N$10)-SUMPRODUCT(--(LEFT($D$2:$D$10,6)="Cancel"),--($E$2:$E$10=E2),$I$2:$I$10,$N$2:$N$10)<0,0,I2*N2))

Open in new window


copied down
0
 

Author Comment

by:broadcastwarehouse
Comment Utility
Yes it is a bit confusing, it is hard to explain what is needed.
This Formula does put a Zero if the Actual Quatity is less that the Canceled total qty as I asked.
But it has now stopped working the other way around.

It needs to do both, when the Actual QTY is less than Combined Cancel lines it needs to show Zero. (as it does now with your new formula)
When it is Higher it needs to show the difference (as the previous formula did)
Does that make sense?
0
 
LVL 23

Expert Comment

by:NBVC
Comment Utility
Let's see if this is the winning formula:

=IF(AND(D2="New P/O",(SUMPRODUCT(--($D$2:$D$10="New P/O"),--($E$2:$E$10=E2),$I$2:$I$10,$N$2:$N$10)-SUMPRODUCT(--(LEFT($D$2:$D$10,6)="Cancel"),--($E$2:$E$10=E2),$I$2:$I$10,$N$2:$N$10))<0),0,IF(D2="New P/O",ABS(SUMPRODUCT(--($D$2:$D$10="New P/O"),--($E$2:$E$10=E2),$I$2:$I$10,$N$2:$N$10)-SUMPRODUCT(--(LEFT($D$2:$D$10,6)="Cancel"),--($E$2:$E$10=E2),$I$2:$I$10,$N$2:$N$10)),IF(SUMPRODUCT(--($D$2:$D$10="New P/O"),--($E$2:$E$10=E2),$I$2:$I$10,$N$2:$N$10)-SUMPRODUCT(--(LEFT($D$2:$D$10,6)="Cancel"),--($E$2:$E$10=E2),$I$2:$I$10,$N$2:$N$10)<0,0,I2*N2)))

Open in new window

0
 

Author Comment

by:broadcastwarehouse
Comment Utility
It Works!!! :)
I have a bigger sheet that I need to add these formula's to to make sure they still work.
IF I get further problems I will let you know once I have checked it through.
Thanks again.
0
 
LVL 23

Expert Comment

by:NBVC
Comment Utility
Great.  Let me know the outcome.
0
 

Author Comment

by:broadcastwarehouse
Comment Utility
Hi There,
Thanks for your help so far with this, I can report that it is working correctly.

There is one more thing I need your help with.
I need this formula to work in the column next to the one it currently does as well, effectively I need to sort of shift this formula so that it works in the  "Adjusted Qty x Cost" column as well.

It should be the same calculations, but looking at the Adjusted QTY column instead of the Actual Qty Column.

Doing this will allow me to compare these two columns to see the differences in cost. between ordering the Actual Qty we need against the adjusted Qty it suggests to buy.

Thanks.
0
 
LVL 23

Expert Comment

by:NBVC
Comment Utility
Would that be?

=IF(AND(D2="New P/O",(SUMPRODUCT(--($D$2:$D$10="New P/O"),--($E$2:$E$10=E2),$J$2:$J$10,$N$2:$N$10)-SUMPRODUCT(--(LEFT($D$2:$D$10,6)="Cancel"),--($E$2:$E$10=E2),$J$2:$J$10,$N$2:$N$10))<0),0,IF(D2="New P/O",ABS(SUMPRODUCT(--($D$2:$D$10="New P/O"),--($E$2:$E$10=E2),$J$2:$J$10,$N$2:$N$10)-SUMPRODUCT(--(LEFT($D$2:$D$10,6)="Cancel"),--($E$2:$E$10=E2),$J$2:$J$10,$N$2:$N$10)),IF(SUMPRODUCT(--($D$2:$D$10="New P/O"),--($E$2:$E$10=E2),$J$2:$J$10,$N$2:$N$10)-SUMPRODUCT(--(LEFT($D$2:$D$10,6)="Cancel"),--($E$2:$E$10=E2),$J$2:$J$10,$N$2:$N$10)<0,0,J2*N2)))

Open in new window

0
 

Author Comment

by:broadcastwarehouse
Comment Utility
Yes this seems to work, thanks.
0
 

Author Comment

by:broadcastwarehouse
Comment Utility
Hi there,
I have a further problem with this.
Firstly the current formulas only look at the first 10 lines of the columns, but I need it to look at the entire column as there will be other lines added to this document.
For example - $D$2:$D$10="New P/O" is it as simple as changing it to - D:D="New P/O" ?

Secondly, the lines that have a Cancel need to output  a zero in both the "Actual QTYxCost" and "Adjusted QtyxCost" columns, otherwise the totals at the bottom are incorrect as it adds them up.
0
 
LVL 23

Expert Comment

by:NBVC
Comment Utility
Can we compare to AM column and say that if AM=0 then return 0, otherwise continue with the calcs?

e.g.

=IF(AM2=0,0,IF(AND(D2="New P/O",(SUMPRODUCT(--($D$2:$D$10="New P/O"),--($E$2:$E$10=E2),$J$2:$J$10,$N$2:$N$10)-SUMPRODUCT(--(LEFT($D$2:$D$10,6)="Cancel"),--($E$2:$E$10=E2),$J$2:$J$10,$N$2:$N$10))<0),0,IF(D2="New P/O",ABS(SUMPRODUCT(--($D$2:$D$10="New P/O"),--($E$2:$E$10=E2),$J$2:$J$10,$N$2:$N$10)-SUMPRODUCT(--(LEFT($D$2:$D$10,6)="Cancel"),--($E$2:$E$10=E2),$J$2:$J$10,$N$2:$N$10)),IF(SUMPRODUCT(--($D$2:$D$10="New P/O"),--($E$2:$E$10=E2),$J$2:$J$10,$N$2:$N$10)-SUMPRODUCT(--(LEFT($D$2:$D$10,6)="Cancel"),--($E$2:$E$10=E2),$J$2:$J$10,$N$2:$N$10)<0,0,J2*N2))))

Open in new window


Also,  It is not recommended to use whole columns like D:D with SUMPRODUCT because it is not an efficient function and will slow down the processing significantly.  Instead use a range that will be large enough that it should never be exceeded, but not excessively large.

You can select the two column and do a FIND/REPLACE (ALT+H).  Replace $10 with $1000 or whatever number you choose to be your max range size.
0
 

Author Comment

by:broadcastwarehouse
Comment Utility
Hi there,
This formula doesn't seem to work at all, it says it is a circular formula and turns all of them to 0.

Can you tell me which document you are using and which column you are using this formula in? Just so I am sure that its not my fault!?
0
 
LVL 23

Expert Comment

by:NBVC
Comment Utility
I used the one that shows up first here:

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28349935.html#a39835262

and the formula is put in AN2, and copied down.
0
 
LVL 23

Expert Comment

by:NBVC
Comment Utility
Hi Broadcastwarehouse,

Just checking in to see if this question has been resolved.
0
 

Author Comment

by:broadcastwarehouse
Comment Utility
Hi Sorry but this doesn't work, AN is the cost difference column, did you mean AM?
I tried AM too just in case, but it gives me a Circular reference warning.

if I OK the warning it just makes them all go to 0.
0
 
LVL 23

Expert Comment

by:NBVC
Comment Utility
Sorry, it probably the AL column then....   I am getting confused with the number of changes, etc in this long thread.
0
 

Author Comment

by:broadcastwarehouse
Comment Utility
Sorry this doesn't work, it is no good compring it with the AM column, because the AM column also needs to do the same thing -

The lines that have a Cancel need to output  a zero in both the "Actual QTYxCost"(AN)and "Adjusted QtyxCost" (AM) columns, otherwise the totals at the bottom are incorrect as it adds them up.
0
 
LVL 23

Expert Comment

by:NBVC
Comment Utility
I am afraid I don't know what else to offer.  After 42 postings in this thread, I think I am totally confused as to what the goal is anymore....
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Mac-based software for Excel 8 19
TT Copy Formula 3 15
VBA in SharePoint 3 16
Redacting a row in Excel based on a term. 17 29
Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now