Want MKT Segment and MKT Sub Segment Contribution Reqd

[rush2rajen]

Hi Excel Guru,

I am having a Data 22-23 sheet and a Format Sheet as well as Sub Seg Sheet. I am unable to get a formula wherein I can get MKT Segment and MKT Sub Segment Contribution..

Request you to please help in this regards

Thanks a lot in advance

 

[BobBridges]

If I understand correctly:

1) "% Contribution MKT Sgmt on Cluster AVG" (col I) would be the sum of E, F and G in that row divided by the sum of E, F and G for all the rows having the same "Mkt Segment" (col B). For row 3 that would be, let's see, the sum of E3:G3 divided by the sum of E3:G14.

2) "% Contribution MKT Sub Sgmt on Cluster AVG" (col J) would be the sum of E, F and G in that row divided by the sum of E, F and G for all the rows having the same "Mktg Sub Segment" (col C). For row 3 that's SUM(E3:G3) divided by the SUM(E3:G5).

3) "% Contribution Model Wise on Cluster AVG" (col K) would be SUM(E:G) in that row divided by the sum for all the rows having the same "Cluster".

No wait ... did I get all this wrong? Is what you want the sums of E:G for the segment, subsegment and row divided by the sum for the cluster? Maybe you should tell me what you're looking for.

To write an Excel formula for any of this, you have to do one of two things:

a) Fill in the blank spaces in cols A:C. Then you can use SUMIF to calculate the sums for the correct Cluster, segment and sub-segment.

b) Write a worksheet formula. A worksheet formula may be more work (unless you like that sort of thing, as I do), but if you like the appearance of this worksheet with the blank spaces, which makes it easier to read, then you gotta have a program do some of the thinking for you.

 

[rush2rajen]

If I understand correctly:

1) "% Contribution MKT Sgmt on Cluster AVG" (col I) would be the sum of E, F and G in that row divided by the sum of E, F and G for all the rows having the same "Mkt Segment" (col B). For row 3 that would be, let's see, the sum of E3:G3 divided by the sum of E3:G14.

2) "% Contribution MKT Sub Sgmt on Cluster AVG" (col J) would be the sum of E, F and G in that row divided by the sum of E, F and G for all the rows having the same "Mktg Sub Segment" (col C). For row 3 that's SUM(E3:G3) divided by the SUM(E3:G5).

3) "% Contribution Model Wise on Cluster AVG" (col K) would be SUM(E:G) in that row divided by the sum for all the rows having the same "Cluster".

No wait ... did I get all this wrong? Is what you want the sums of E:G for the segment, subsegment and row divided by the sum for the cluster? Maybe you should tell me what you're looking for.

To write an Excel formula for any of this, you have to do one of two things:

a) Fill in the blank spaces in cols A:C. Then you can use SUMIF to calculate the sums for the correct Cluster, segment and sub-segment.

b) Write a worksheet formula. A worksheet formula may be more work (unless you like that sort of thing, as I do), but if you like the appearance of this worksheet with the blank spaces, which makes it easier to read, then you gotta have a program do some of the thinking for you.

Hi BOB,

I want formula for the same. Can you please help me out. Thanks in Advance

 

[BobBridges]

I'm sorry, I misspoke when I described option b). If you want to use a worksheet formula, I think you have to a) fill in the blank cells in col A:C, which will let you write a worksheet formula for I, J and K using the SUMIF function. If you don't want to fill in the blank cells, you can b) write a worksheet function (not a worksheet formula; my mistake). That option would mean that instead of you using the built-in Excel SUMIF function, you could write a function of your own using the VBA programming language, and then call that function from cols I:K. The program you write would look at cols A:C and figure out for itself which cluster, segment and subsegment are to be used in the calculation.

Unless you know how to program and like that sort of thing (I do), or would like to learn how to write computer programs, you probably want option a. It's not very hard if you like that sort of thing, but if you don't it'll just seem like work.

There's a third option, come to think of it: You can fill in some helping columns at the right with the cluster, segment and subsegment, and let your SUMIF formulae refer to those columns rather than to A:C. That way a human could have the easy reading that comes from looking at A:C, but your formulae in I:K could have the easy work of looking at the helping columns (X:Z, perhaps).

 

[Peter Bartholomew]

Just to add to @BobBridges second paragraph. Provided you are a 365 user, it is quite possible to program using worksheet formulas. It avoids moving between the sheet and VBA or TypeScript coding environments, but would still be pretty heavy going for a traditional spreadsheet user. For example the formula
= SCAN("cluster", Cluster, LAMBDA(list,code, IF(LEN(code), code, list)))
would fill down the data in the cluster column, either to create a helper column or, better, to provide the values for a LET local variable.
The list of distinct clusters could be prepared manually, but the formula
= FILTER(Cluster, (LEN(Cluster)>0) * ISERROR(FIND("TOTAL", Cluster)))
would achieve the same objective dynamically.

Of course, like pretty much any formula of significance, the first formula should be prepared as a named Lambda function 'FillDownλ' (say) so that the resulting formula reads
= FillDownλ("Cluster", Cluster)
The advantage of doing things this way is that
= FillDownλ("Segment", Mkt_Segment)
follows naturally.
The various statistics you require would be returned by a formula that uses these columns to FILTER the overall data set, prior to averaging.

 

[Peter Bartholomew]

Just to continue the story a bit ...
Defining a Lambda function Volumeλ will allow you to produce totals such as 154,
= Volumeλ("AND", "Hatch")
where the function is defined by

= LAMBDA(aCluster, aMkt_Segment,
    LET(
      filledCluster,  FillDownλ("cluster", Cluster),
      filledSegment,  FillDownλ("Segment", Mkt_Segment),
      filteredVolume, FILTER(volume, (filledCluster=aCluster)*(filledSegment=aMkt_Segment)),
      SUM(filteredVolume)
    )
  )