Aug 26 2021 11:35 AM - edited Aug 26 2021 12:32 PM
Aug 26 2021 11:35 AM - edited Aug 26 2021 12:32 PM
Hello all! I'm working on a report (per company policy can't share) on which I would like to optimize with formulas. I made a dummy example as close as I could so I could explain myself better for a specific case I'm having trouble with.
Looking to build a formula to do the following:
Search for the ID number on the master list ONLY IF:
On column [Repeated?] it has FALSE, AND on column [MODE] it has anything other than "COLLECT" / Otherwise return a 0.
-If found, retrieve the COST (if the number repeats then sum all costs for matching ID)
-If the ID is not on the master list then the cost must be calculated:
Sum the weight on all matching IDs and multiply that for the corresponding RATE matching the MODE
I'm showing what I have built so far, and the cost calculation works but cannot make the highlighted part work/not sure how to add it.
Also, I would like to implement a minimum for below cases:
For GROUND IDs total cost may not be below 5.28, if the calculation falls below that it must return 5.28
For LTL IDs total cost may not fall below 86.11 so it must return 86.11 if it does.
I'm open to all suggestions as well as questions if something is not clear enough.
Thanks in advance!!
Aug 26 2021 12:15 PM - edited Aug 26 2021 12:15 PM
Since it's a dummy file to begin with, it would be far more helpful to post that dummy file than just an image of it. That way we could work with the file rather than having to re-create it.
What version of Excel do you have? Are you able to use the new functions like UNIQUE, or are you identifying the duplicates ("repeated") manually? The most recent version of Excel is needed for that function (and a few others) to operate.
Aug 26 2021 12:33 PM
Aug 26 2021 01:33 PM
I'm not able to follow your explanation as to what you're trying to calculate. So let me send this back to you to suggest another function that might be helpful; feel free, though, to come back with clarification.
I entered this formula in cell O2: =SORT(UNIQUE(B2:C22))
to get this array of unique IDs and associated modes (UNIQUE does require the most current version of Excel, so if it doesn't work, let us know)
Beyond that, though, it's not clear to me what the relationship is between all your tables and what you're trying to calculate. I think my UNIQUE list has eliminated the need to look for FALSE in your IF function...but, as I said, it's not clear to me. I'd like to make it a more elegant solution if possible, as I'm sure you would too.
Aug 26 2021 01:34 PMSolution
If I understood the logic correctly that could be
=LET( getSum, IF( ISNA( XMATCH([@ID],MasterList[Master list]) ), XLOOKUP([@MODE], Rates[MODE], Rates[RATE]*SUMIF([ID],[@ID],[WEIGHT]), FALSE,0,1), SUM( FILTER( MasterList[Cost], MasterList[Master list] = [@ID]) ) ), adjustedSum, IFS( [@MODE] = "Ground", MAX( 5.28, getSum), [@MODE] = "LTL", MAX( 86.11, getSum), TRUE, getSum), IF( AND( [@[Repeated?]]=FALSE, [@MODE]<>"collect"), adjustedSum, 0) )
Aug 26 2021 01:36 PM
I think there is a mistake in your data. The master list contains a duplicate.
Aug 26 2021 01:54 PM
My understanding that is by design. If ID:s are repeated in Master List we calculate sum of cost for all such duplicates.
Aug 27 2021 06:37 AM
Aug 27 2021 07:26 AM
Apparently @Sergei Baklan understands the spreadsheet and your intent. If his formula works for you, I'm happy to step out of the picture.
I still don't get it. Let me, as best I can, give the source of my confusion.
I do understand that the purpose is ultimately to figure out costs to be attributed to each account.
And maybe this is just my obsessive compulsive nature, but you are so close to it I think you take for granted the context. It's the "big picture" that's not clear. I have no idea what all this represents, and for me at any rate, knowing what that big picture is helps me navigate the details.
Aug 27 2021 01:08 PM
Aug 28 2021 03:59 AM
@Detlef Lewin , I don't know what is the logic behind, but it's possible to do the calculation with duplicates. Not as easy as with unique ID:s, but it works.
Aug 30 2021 01:00 PM
Aug 30 2021 02:39 PM
You may but I see no one reason to do that. Structured table automatically add formulas to new rows. It doesn't matter in which sheet you place the table, only table name is used.
Thus you don't need enter formulas to new rows, just add your data and table does the rest.
However, you may stay on the table, convert it to the range from menu and formula will be adjusted automatically. Reference on entire column in formulae is always not a good idea, obvious side effect is performance. To improve it you may use dynamic ranges like
=LET( masterListID, $L$2:INDEX($L:$L,COUNTA($L:$L)), masterListCost, $M$2:INDEX($M:$M,COUNTA($M:$M)), mode, $I$2:INDEX($I:$I,COUNTA($I:$I)), rate, $J$2:INDEX($J:$J,COUNTA($J:$J)), ID, $B$2:INDEX($B:$B,COUNTA($B:$B)), weight, $D$2:INDEX($D:$D,COUNTA($D:$D)), getSum, IF( ISNA( XMATCH($B2,masterListID ) ), XLOOKUP( $C2, mode, rate*SUMIF(ID,$B2,weight), FALSE,0,1), SUM( FILTER( masterListCost, masterListID = $B2) ) ), adjustedSum, IFS( $C2 = "Ground", MAX( 5.28, getSum), $C2 = "LTL", MAX( 86.11, getSum), TRUE, getSum), IF( AND( $E2=FALSE, $C2<>"collect"), adjustedSum, 0) )
but in any case you shall be sure nothing is added below main data in columns you use. Please check in attached.
In general structured tables were made to improve calculations in cases like yours. But what to use is always your decision.
Aug 31 2021 08:35 AM
Awesome! yes I also believe a table would be a more efficient way of dealing with the data, so I'm definitely gonna propose that change to our reports. One last thing I would like help with would be to update the formula to look into different sheets for the complementary tables, this so I can translate the formula to our main workbook as smoothly as possible. My knowledge on Excel is still limited so I'm having some trouble understanding some components on your formula.
New dummy attached.
Aug 31 2021 12:51 PM
Formula will be
=LET( masterListID, Sheet3!$A$2:INDEX(Sheet3!$A:$A,COUNTA(Sheet3!$A:$A)), masterListCost, Sheet3!$B$2:INDEX(Sheet3!$B:$B,COUNTA(Sheet3!$B:$B)), mode, Sheet2!$A$2:INDEX(Sheet2!$A:$A,COUNTA(Sheet2!$A:$A)), rate, Sheet2!$B$2:INDEX(Sheet2!$B:$B,COUNTA(Sheet2!$B:$B)), ID, $A$2:INDEX($A:$A,COUNTA($A:$A)), weight, $C$2:INDEX($C:$C,COUNTA($C:$C)), getSum, IF( ISNA( XMATCH($A2,masterListID ) ), XLOOKUP( $B2, mode, rate*SUMIF(ID,$A2,weight), FALSE,0,1), SUM( FILTER( masterListCost, masterListID = $A2) ) ), adjustedSum, IFS( $B2 = "Ground", MAX( 5.28, getSum), $B2 = "LTL", MAX( 86.11, getSum), TRUE, getSum), IF( AND( $D2=FALSE, $B2<>"collect"), adjustedSum, 0) )
Please check in attached.