Calculating a Grand Total (with IF/THEN Logic)

[bdav1216]

Hello,

I need to create a 'grand total' in the in attached spreadsheet, and can use some assistance.

In the attached example, I put the expected values for the first 3 months into the ‘BBB Incident Detail’ column. Please note that Column N (Total Weight) is being calculated in my actual column, but, for this example, the values are being pasted.

In the tab ‘BBB Incident Detail’:
For each month (January through August), calculate the grand total adding the ‘Total Weight’ in the ‘Log’ tab to the logic below:
- If the same customer (Column A) has greater than/equal to two Incidents (Column D = “Incident”) in a 3 month period (current month and previous 2 months), then ADD 1 to the grand total.
- If the same customer (Column A) has greater than/equal to two High Incidents (Column D = “High Incident”) in a 3 month period (current month and previous 2 months), then ADD 2 to the grand total.
- If the same customer (Column A) has greater than/equal to two for a combination of ‘Incident’ or ‘High Incident’ (Column D), add 1.5 to the grand total.

January’s ‘Total Weight’ is 2.5.
January has 1 customer (CDE Finance) with 3 incidents in the last 3 months (January is not the best example because this is only for 2017 data), but would still qualify. In this scenario, according to the above logic (1st scenario), then add ‘1’ to the grand total, making January a grand total of 3.5

February’s ‘Total Weight’ is 1.0
Both customer had only 1 incident in the current month and nothing in January; thus, no additional points are added to February’s grand total.

March’s ‘Total Weight’ is 1.75
Both customer had only 1 incident in the current month and nothing in January; thus, no additional points are added to February’s grand total.
ABC Finance has had 1 ‘incident’ and 1 ‘High Incident’ (the last scenario) for a combination of 2 in the past 3 months so ‘ADD 1.5’ to the total, making the grand total of 3.25

Hopefully, this will make sense and thank you in advance!!!

[bdav1216]

Hopefully, all of this made sense, but let me know today if I can help answer any questions.

[Richard Buttrey]

There is something about your file that is throwing up a warning causing it to open in Protected View implying Excel believes there is something unsafe about it.

Would you please revisit the file and remove whatever it is that is causing this warning.

[bdav1216]

Thank you, I re-posted a new version, not sure what happened with the other version.

[bdav1216]

Please let me know if you have any questions with the updated version that I attached.

[Richard Buttrey]

Hi,

I think the best approach is to add some helper columns to the log sheet.
I've added them in the attached but since you haven't said whether there is any priority to the three rules, or whether they are mutually inclusive I doubt the formulae I've included is correct in all circumstances.

However hopefully you can see the idea. The four columns only have figures for each unique occurrence of month and customer so that you can use helper column R to populate the Incident detail sheet when you've applied the right formulae in columns O:Q.

[bdav1216]

Thank you Richard. Is there a way to aggregate that total, using the helper columns to display the grand total in 'BBB Incident Detail'? Ultimately, I do want to get to that grand total in a format for each month.

As far as priority, it would be possible (though unlikely) for all of these to occur, but the 3rd point should go first, followed by the next two (which are based on "Incident" vs. "High Incident")

- If the same customer (Column A) has greater than/equal to two Incidents (Column D = “Incident”) in a 3 month period (current month and previous 2 months), then ADD 1 to the grand total.
- If the same customer (Column A) has greater than/equal to two High Incidents (Column D = “High Incident”) in a 3 month period (current month and previous 2 months), then ADD 2 to the grand total.
- If the same customer (Column A) has greater than/equal to two for a combination of ‘Incident’ or ‘High Incident’ (Column D), add 1.5 to the grand total.

[bdav1216]

Other question.

In the attached spreadsheet:

January has 2 'High Incident' and 1 'Incident'.

Column Q is correctly calculating 3.25 points based on the aggregate weights for 3 orders in January (1.5 + 1.5 + 0.25).

If there are GE 2 'Incident' (Column O), i want to add '1' to the grand total of points for ALL orders in January. Because this month only has 1 'incident' in January, no additional points should be added to the grand total of 3.25 for January.

However, there are 2 'High Incident' in January. In this scenario, if there are GE 'High Incident' (Column P), I should add '2' to the grand total of 3.25 in January, meaning the total for January is now 5.25.

Column R is actually Checking Column O and Column P for the occurrences mentioned above, but it doesn't provide a grand total, instead a column by column view that ends up double aggregating.

Ultimately, i need my 'BBB Incident Tab' to provide a grand total for each month.

How do I go about doing this?

[bdav1216]

Anyone who can add some help here?

[bdav1216]

Bumping up for the morning crowd!

[bdav1216]

One last bump, really just looking for any help that be given. Thank you in advance.

[Richard Buttrey]

Would you manually add the final results you expect noting the basis of the calculation in say column S of the Log sheet.
i.e. on each row where a new combination of Customer & Months starts, rows 2,4,6,7,11,12,14,16,18

[bdav1216]

See the attached .xls

I need to apply the following logic below. How do I go about doing this?

Step 1:

If the 'Order Number' column in Sheet1 matches to the 'Order Number' column in Sheet2, output following in the Classify column (Sheet1):

'1' if Column B (Type) of Sheet2 = "Incident"
'2' if Column B (Type) of Sheet2 = "High Incident"
'0' if no match

Step 2:

If the 'Classify' column in Sheet1 = "1", then multiple (Column C * 0.25) in Column D (Adjustment)
If the 'Classify' column in Sheet1 = "2" then output the value of Column C in Column D (Adjustment)
If the 'Classify' column in Sheet1 = "0", then output a '0' in Column D (Adjustment)

Thank you!

[benishiryo]

i've not read the questions posted here but was brought here due to the other thread being flagged as a duplicate.
https://www.excelforum.com/excel-for...ml#post4745222

so based on that file posted there, try:
=IF(IFERROR(VLOOKUP(A2,Sheet2!A:B,2,0),3)="Incident",C2*0.25,IF(IFERROR(VLOOKUP(A2,Sheet2!A:B,2,0),3)="High Incident",C2,0))

[bdav1216]

i've not read the questions posted here but was brought here due to the other thread being flagged as a duplicate.
https://www.excelforum.com/excel-for...ml#post4745222

so based on that file posted there, try:
=IF(IFERROR(VLOOKUP(A2,Sheet2!A:B,2,0),3)="Incident",C2*0.25,IF(IFERROR(VLOOKUP(A2,Sheet2!A:B,2,0),3)="High Incident",C2,0))

thank you!

What does the " '),3)' " mean in your formula?

[benishiryo]

you're welcome. it actually doesn't matter. it's for the scenario where Order Number is not found and results in an error, what do i want to show. I input 3 (as in 3rd option). You suggested "0". but it will not reach a stage where 3 or 0 matters. the 1st IF check if the results is "Incident". if it's not, it checks if it's "High Incident". Otherwise, it will return 0. Hence, it does not do a 3rd check to see if it's 3 or 0.

[bdav1216]

One additional logic update using the updated attached workbook:

=IF(IFERROR(VLOOKUP(A2,Sheet2!A:B,2,0),3)="Incident",B2*0.25,IF(IFERROR(VLOOKUP(A2,Sheet2!A:B,2,0),3)="High Incident",B2,0))

IF this is the 2nd (or greater) customer "High Incident" in 3 months (current month and previous 2 months), multiply the order weight by 1.5 (instead of passing the value of the Order Weight).

IF this is the 2nd customer "Incident" in 3 months (current month and previous 2 months), instead of multiplying by 0.25, just pass the full value of the Order Weight.

[bdav1216]

Bump for the late night crowd!

[Richard Buttrey]

Please add the expected results. For the avoidance of misunderstanding we always like to know what the the end goal actually is

[bdav1216]

See the attached with updated (expected) results hard coded in Column D.

A check is needed vs. Sheet2 to determine if this customer has had either GE 2 1 "incident" or "high incident" within 3 months. If that is not the case, the formula below would apply, but if the customer has had previous incidents, then the below weights would need to be applied.

=IF(IFERROR(VLOOKUP(A2,Sheet2!A:B,2,0),3)="Incident",B2*0.25,IF(IFERROR(VLOOKUP(A2,Sheet2!A:B,2,0),3)="High Incident",B2,0))

IF this is the 2nd (or greater) customer "High Incident" in 3 months (current month and previous 2 months), multiply the order weight by 1.5 (instead of passing the value of the Order Weight).

IF this is the 2nd customer "Incident" in 3 months (current month and previous 2 months), instead of multiplying by 0.25, just pass the full value of the Order Weight.

[bdav1216]

Any questions on the above?

[Richard Buttrey]

In much the same way as my earlier post #6 with example showed first add

E2 on Data Sheet2 amd copy down.

Please Login or Register  to view this content.

Now C2 on the Resulst sheet copied down

Please Login or Register  to view this content.

[bdav1216]

In much the same way as my earlier post #6 with example showed first add

E2 on Data Sheet2 amd copy down.

Please Login or Register  to view this content.

Now C2 on the Resulst sheet copied down

Please Login or Register  to view this content.

Question -

Why wouldn't customer AAA in Column 8 and 9 have a value? These would be the 2nd and 3rd high incidents for customer AAA in a 3 month time period. It does appear to be calculating them correctly in Sheet1, but trying to understand that difference.

How is this formula actually working so that they are being multiplied by 1.5

[bdav1216]

Bump!

My current formula is outputting a value (when possible), but otherwise a #NA.

How would I change this formula to output a '0' when a value isn't able to be calculated?

=IF(IFERROR(VLOOKUP(B516,ReClassify!AU:AV,2,0),3)="Incident",DH516*0.25,IF(IFERROR(VLOOKUP(B516,ReClassify!AU:AV,2,0),3)="High Incident",DH516,0))*IF(INDEX(ReClassify!AY:AY,MATCH(B516,ReClassify!AU:AU,FALSE),1)>=2,1.5,1)

[bdav1216]

Bump!

My current formula is outputting a value (when possible), but otherwise a #NA.

How would I change this formula to output a '0' when a value isn't able to be calculated?

=IF(IFERROR(VLOOKUP(B516,ReClassify!AU:AV,2,0),3)="Incident",DH516*0.25,IF(IFERROR(VLOOKUP(B516,ReClassify!AU:AV,2,0),3)="High Incident",DH516,0))*IF(INDEX(ReClassify!AY:AY,MATCH(B516,ReClassify!AU:AU,FALSE),1)>=2,1.5,1)

Any around tonight who could help resolve?

[Richard Buttrey]

Your sample did not result in any #N/A

Since your actual data seemingly does then add an IFERROR to the last element. i.e.

Please Login or Register  to view this content.