Logic Updates to existing formulas (duplicates, exclusion, lookups)

[bdav1216]

See the attached workbook:

I am requesting assistance with a few different updates to existing logic, which I will explain.

Update 1:

Two logic updates to Column Q:
- update to avoid counting duplicate values
- exclude counting when 'rej' is listed in description:

In the 'SHIP' tab:
When Column A has the same value, Column D = "Final" and Column I = "C D w/ I"
Column Q aggregates the values in Column E

Is there a way to update this formula to avoid counting duplicate values in Column Q?

I listed two example: 1163603 and 1172600 with the expected value at the end of each row.

Also, for the Column Q logic update, do not include those rows that have the word "Rej" within Column K. If "Rej" is found within the description, do not include any values in Column E in the calculation.

Example: 1179778


Update 2 :
In the 'Pre' Tab:

Column U is currently performing a calculation based on the data from Column T.
How would this formula be updated to include two additional piece of logic.

First logic update:
If Column E = "AAA" or "BB OF B" then output "PRE CALC" IN Column T else perform the calculation.

I have the values needed for this check in the tab called "Look" as I would prefer this to be a look-up in the formula.

See examples: 1226572 and 1226191 in the attached workbook for this example.

Second logic update:
When Column T has a value of 1, do not peform the calculation; output '-' instead of the calculated value.

See Example: 1224113 in the attached workbook for this example.

[Bo_Ry]

Please try

Update 1:
=AVERAGEIFS($E$2:$E$14,$A$2:$A$14,A2,$D$2:$D$14,"Final",$I$2:$I$14,"C D w/ I",$K$2:$K$14,"<>*rej*")

or
=SUM(IF(FREQUENCY(IF($A$2:$A$14=A2,IF($D$2:$D$14="Final",IF($I$2:$I$14="C D w/ I",IF(ISERROR(SEARCH("Rej",$K$2:$K$14)),$E$2:$E$14)))),--$E$2:$E$14),$E$2:$E$14))

Update 2:
=IF(ISNA(MATCH(E3,{"AAA";"BB of B"},)),IF(T3=1,"-",T3*25%%%),"Pre Calc")

[Bo_Ry]

You didn't even try my 2nd formula

or
=SUM(IF(FREQUENCY(IF($A$2:$A$14=A2,IF($D$2:$D$14="Final",IF($I$2:$I$14="C D w/ I",IF(ISERROR(SEARCH("Rej",$K$2:$K$14)),$E$2:$E$14)))),--$E$2:$E$14),$E$2:$E$14))

Please try
Update 1:
=AVERAGEIFS($E$2:$E$14,$A$2:$A$14,A2,$D$2:$D$14,"Final",$I$2:$I$14,"C D w/ I",$K$2:$K$14,"<>*rej*")

or
=SUM(IF(FREQUENCY(IF($A$2:$A$14=A2,IF($D$2:$D$14="Final",IF($I$2:$I$14="C D w/ I",IF(ISERROR(SEARCH("Rej",$K$2:$K$14)),$E$2:$E$14)))),--$E$2:$E$14),$E$2:$E$14))

[bdav1216]

You didn't even try my 2nd formula

Hello,

I did try your 2nd formula. For ID 1163603, it calculates a value of 473,870,642. I am expecting that value to be 302,404,597 because there is a duplicate value in Column E for that ID (based on Column A).

[Bo_Ry]

Hello,

I did try your 2nd formula. For ID 1163603, it calculates a value of 473,870,642. I am expecting that value to be 302,404,597 because there is a duplicate value in Column E for that ID (based on Column A).

File from Post# ID 1163603 looks like 302,404,597 to me

2021-02-21 23_04_18-SampleP.xlsx - Excel.png

[bdav1216]

File from Post# ID 1163603 looks like 302,404,597 to me

Yes, this formula is looking good upon my initial review:

=SUM(IF(FREQUENCY(IF($A$2:$A$14=A2,IF($D$2:$D$14="Final",IF($I$2:$I$14="C D w/ I",IF(ISERROR(SEARCH("Rej",$K$2:$K$14)),$E$2:$E$14)))),--$E$2:$E$14),$E$2:$E$14))

For the 'PRE' tab, how would that formula be updated to output a "-" when the calculation has a value of "VALUE!"? An example was: 1226191 where Columnt T
is currently equal to '-', resulting in that 'VALUE'!" error?

Existing formula:
=IF(ISNA(MATCH(E3,{"AAA";"BB of B"},)),IF(T3=1,"-",T3*25%%%),"Pre Calc")

[bdav1216]

Please try

=SUM(IF(FREQUENCY(IF($A$2:$A$14=A2,IF($D$2:$D$14="Final",IF($I$2:$I$14="C D w/ I",IF(ISERROR(SEARCH("Rej",$K$2:$K$14)),$E$2:$E$14)))),--$E$2:$E$14),$E$2:$E$14))

One other question:

For this formula:

=SUM(IF(FREQUENCY(IF($A$2:$A$14=A2,IF($D$2:$D$14="Final",IF($I$2:$I$14="C D w/ I",IF(ISERROR(SEARCH("Rej",$K$2:$K$14)),$E$2:$E$14)))),--$E$2:$E$14),$E$2:$E$14))

How would I remove the logic to check for "Rej"? The rest of the logic should stay the same.

[Glenn Kennedy]

Average... of course!! nice lateral thinking, there...

[bdav1216]

Thank you!

The logic for Updates 2 are looking good!

One update -> I do receive #VALUE! errors in some calculations. How would I update the formula you provided to address that?

For Updates 1, using Average is causing issues because there could be different values (not always duplicated values). For example, if Column E has 10 and 10, the 10 for average makes sense, but if the values are 10 and 2, instead of calculating '12', it's calculating a value of 6.

Is there a way to update this formula aggregate only the unqiue values (vs. taking the average)?

Also, I am receiving a #DIV/0! error when there are no aggegrations possible for an ID. How would I update that formula to have a '-' present

I can provide a few more examples.

[Glenn Kennedy]

Do. There's not much point in telling us what we can't see...

[bdav1216]

Thank you!

In the 'PRE' tab, I added one new example (ID: 1226191) where the updated formula is outputting a #VALUE!
Can the exisiting formula be updated to output '-' instead of #VALUE!

For the 'SHIP' tab:
1154375 where current formula results in #DIV/0!
Can the exisiting formula be updated to output '-' instead of #DIV/0!?

1155626 and 1162502 are examples where there different values that should be aggregated, not an average. I understand why average works when you have the same values, but it doesn't work when you have different values. These examples will show different values and the expected result.

[kvsrinivasamurthy]

This is for update 1
In Q2 then copied down.

=IF(COUNTIFS($A$2:$A2,A2,$D$2:$D2,"Final",$I$2:$I2,"C D w/ I",$K$2:$K2,"<>*Rej*")=1,SUMIFS($E$2:$E$14,$A$2:$A$14,A2,$D$2:$D$14,"Final",$I$2:$I$14,"C D w/ I",$K$2:$K$14,"<>*Rej*"),"")

Is this ok. Check for rows 11 and 12.

[bdav1216]

This is for update 1
In Q2 then copied down.

=IF(COUNTIFS($A$2:$A2,A2,$D$2:$D2,"Final",$I$2:$I2,"C D w/ I",$K$2:$K2,"<>*Rej*")=1,SUMIFS($E$2:$E$14,$A$2:$A$14,A2,$D$2:$D$14,"Final",$I$2:$I$14,"C D w/ I",$K$2:$K$14,"<>*Rej*"),"")

Is this ok. Check for rows 11 and 12.

Hello

I did try your 2nd formula. For ID 1163603, it calculates a value of 473,870,642. I am expecting that value to be 302,404,597 because there is a duplicate value in Column E for that ID (based on Column A).

ID: 1163603 has the same issue as 1172600.
ID: 1172600 should output 50,039 (not 100,078) because both of the qualifiying rows have the same value (50,039). When this occurs (same values), I only want to aggregate one row. I should only aggregate when there are different values for qualifying rows. For example, if one value was 50,039 and the other was 10, then it should aggregate to 50,049
ID: 1179778 is accurate
ID: 1224113 is accurate
ID: 1226191 is accurate
ID: 1226572 is accurate

Is there a way to account for these scenarios?

[bdav1216]

For both scenario's, I do also want to account for a default of '-' when there is no calculation available to avoid #VALUE or #DIV/0! also.

[kvsrinivasamurthy]

This is for update 1
In Q2 then copied down

=IF(COUNTIFS($A$2:$A2,A2,$D$2:$D2,"Final",$I$2:$I2,"C D w/ I",$K$2:$K2,"<>*Rej*")=1,SUMPRODUCT($E$2:$E$14*($A$2:$A$14=A2)*($D$2:$D$14="Final")*($I$2:$I$14="C D w/ I")*(ISERROR(SEARCH("*Rej*",$K$2:$K$14))))/SUMPRODUCT(($A$2:$A$14=A2)*($D$2:$D$14="Final")*($I$2:$I$14="C D w/ I")*(ISERROR(SEARCH("*Rej*",$K$2:$K$14)))),"")

[bdav1216]

This is for update 1
In Q2 then copied down

Thank you, I will take a look at this shortly. I appreciate the help.

If you're able to take a look at that 'PRE' tab question around the '-' default for the "VALUE!" issue, let me know. Thank you again!


For the 'PRE' tab, how would that formula be updated to output a "-" when the calculation has a value of "VALUE!"? An example was: 1226191 where Columnt T
is currently equal to '-', resulting in that 'VALUE'!" error?

Existing formula:
=IF(ISNA(MATCH(E3,{"AAA";"BB of B"},)),IF(T3=1,"-",T3*25%%%),"Pre Calc")

[kvsrinivasamurthy]

Not clear about explanation. On Guess, in " PRE" tab
Formula in T3 then copied down

=IF(OR($E3="AAA",$E3="BB of B"),"Pre calc",IFERROR(IF(AND(D3="Net Down",L3>0,ISNUMBER(P3),ISNUMBER(R3)),MAX(O3:P3)-R3,IF(L3<>0,MAX($O3:$P3)-$R3,"-")),"-"))

[bdav1216]

Not clear about explanation. On Guess, in " PRE" tab
Formula in T3 then copied down

Thank for you taking a look.

Let me clarify for you.

In the attached examples on the 'PRE' tab, Column U is outputting a '#VALUE!' when Column T defaults to a '-'.

I need the logic for Column U updated, not Column T, to also output a '-' when the calculation cannot be performed.

ID: 1226191 is an example where Column T is ouptting a '-', but Column U is not able to be calcluated, resulting in the '#VALUE!' error.

Current Logic in Column U:
=IF(ISNA(MATCH(E4,{"AAA";"BB of B"},)),IF(T4=1,"-",T4*25%%%),"Pre Calc")

[kvsrinivasamurthy]

Formula in U3 is

=IFERROR(IF(T3="-","-",T3*0.000025),"-")

Not what you are showing in post#17