Calculate Min and max off the outcome of a Qoutient

[Kennybaby93]

I have quite a big excel table and im unable to upload it due to work privacy reasons. My problem is i currently have a list table under a rawdata table. I then have a cover page with a drop down to select state and animals.
At the moment my table has 6 columns, Im unable to change this or duplicate the table as it comes from a data base I don't have access to but all the ranges would stay the same. I ignore column 2.
The Below sum is of the average of the KG/Amount.
So I first match the ID with the ID. Then match the Left 2 chars of the _tema (This is Date, Reason I am selected the left two is its in format YYMM) then select right 2 off a selected cell that says 2017. so _tema001=17, then match the State with a cell. (The states are inputted as numbers. so I have a lookup table and then a lookup cell and that is referencing that. It then matches the IDW name with the Animal KG. and sums the amount of the KG. IT then divides that with the same but the IDW name is Animal No.
So in the end it just tells me the individual weight for the animal. Im now wanting to find the Min and Max an individual animal that meets the matches but cant work out how to do this.
I have provided and example of the first 4 rows
unitid FS MPSTATE _TEMA001 IDWNAME CCV
AGR14007284 39 3 1610 AGSLAUCATLMNOHEF 69
AGR14007284 39 3 1610 AGSLAUCATLMKGHEF 32219
AGR14062725 39 1 1610 AGSLAUCATLMNOHEF 1945
AGR14062725 39 1 1610 AGSLAUCATLMKGHEF 387198

QUOTIENT(SUMPRODUCT((rawdatatable[unitid]=rawdatatable[unitid])*(LEFT(rawdatatable[[_TEMA001]:[_TEMA001]],2)=RIGHT(I$4,2))*(rawdatatable[MPSTATE]=RawData!$T$8)*(rawdatatable[IDWNAME]=RawData!$U$17)*(rawdatatable[CCV])),SUMPRODUCT((LEFT(rawdatatable[[_TEMA001]:[_TEMA001]],2)=RIGHT(I$4,2))*(rawdatatable[MPSTATE]=RawData!$T$8)*(rawdatatable[IDWNAME]=RawData!$T$17)*(rawdatatable[CCV])))

 

[Peter Bartholomew]

Hi
Turning your example data into a workbook would assist anyone who might be thinking of helping you. Populating the cells on the RawData sheet would then be useful.

At the moment my table has 6 columns, I'm unable to change this or duplicate the table as it comes from a data base I don't have access to but all the ranges would stay the same. I ignore column 2.

I do not understand why you cannot duplicate a dataset merely because it comes from a database. Transforming the data with PowerQuery could produce a far more digestible layout which would only require a simple refresh when you receive new data.

The transformations I have in mind would be to change this alternating pattern of records to one in which the alternate fields are joined to give 'Weight' and 'Number' fields within a single record. At the same time you could remove unused fields and separate the date code to 'year' and 'month'.

You could then load to a new table and revert to Excel formulas to extract the required information or you could perform the division and filtering in PQ and output the result. Meanwhile, to return to where you are rather than where you could be ...

 

[Peter Bartholomew]

… Your formula

'QUOTIENT(
  SUMPRODUCT(
    (rawdatatable[unitid]=rawdatatable[unitid])*
    (LEFT(rawdatatable[[_TEMA001]:[_TEMA001]],2)=RIGHT(I$4,2))*
    (rawdatatable[MPSTATE]=RawData!$T$8)*
    (rawdatatable[IDWNAME]=RawData!$U$17)*
    (rawdatatable[CCV])
  ),
  SUMPRODUCT(
    (LEFT(rawdatatable[[_TEMA001]:[_TEMA001]],2)=RIGHT(I$4,2))*
    (rawdatatable[MPSTATE]=RawData!$T$8)*
    (rawdatatable[IDWNAME]=RawData!$T$17)*
    (rawdatatable[CCV])
  )
)

has a number of curious features.
What does the term
(rawdatatable[unitid]=rawdatatable[unitid])
achieve?

Why have you used a field range notation
(LEFT(rawdatatable[[_TEMA001]:[_TEMA001]],2)=RIGHT(I$4,2))
to return a single field?

The main problem I had though was that there is only one calculated weight (unless there are further pairs of records with the same 'unitid') so I do not see how you can calculate a MAX or MIN.

 

[Peter Bartholomew]

Just to show what can be done by a non-expert using PowerQuery.
It will be so much easier to process the data if you are able to sort the mess before using formulae to filter the data for your cover page.

(Even the filtering could be done using PQ by refreshing the query whenever you change the filter criteria. That way you would not even have to admit to management that you transformed their horrible raw data table.)

 

[Kennybaby93]

Im wanting the Max/Min based on the Animal and animal per Year, not the Unit Id, The reason the Unit ID=Unit ID is because there is over 40,000 rows in my table and I need to make sure that the total/weight is being divided by the correct cells. due to limitation with my work im unable to create a table from the information. Otherwise I would. I also have the limitation that my work is still on excel 2010 which I cant upgrade.

Why have you used a field range notation
(LEFT(rawdatatable[[_TEMA001]:[_TEMA001]],2)=RIGHT(I$4,2))
to return a single field?

it doesn't return a single field. As my table has over 40,000 it spans over 4 years at the moment, So the formula above selects the left 2 of my table 1710 (October 2017) which matches my calculation header of 2017.

 

[Peter Bartholomew]

Hi

I was rather hoping that someone with more data analysis experience might chip in! Is the alternating pattern of 'NO' and 'KG' a consistent feature of your imported data? If so, I believe it could still be better to restructured your date by building a better layout on a fresh worksheet before trying to build formulas to aggregate your data. The objectives would be to flatten the alternating pattern in which the CCV field may contain either of two completely distinct attributes. At the same time you could split the _TEMA field into Year and Month fields.

The new format would allow you to use SUMIFS rather than the SUMPRODUCT array formula. That, in turn, would allow you to build the Kg/No calculations as an array which could be fed directly into the MAX/MIN formulas rather than having to rely upon further tables of helper cells.

I can't help feeling that you have been given a complicated task based on poorly formatted data and have not been provided with the correct tools to do the job. Power Query can be downloaded free of charge from Microsoft and works with Office 2010. Alternatively issuing you with Office 2016 or Office 365 specifically for use on this task would be cheap compared with the cost of employing/wasting your time to turn 'pigs ears into silk purses'. Either way PowerQuery was written by the Microsoft Database team to perform precisely the sort of task you are attempting and it does it far better than Excel formulas.

I still suspect that the term
(rawdatatable[unitid]=rawdatatable[unitid])
will generate 40,000 TRUEs and make no difference whatsoever to your calculation. Similarly rawdatatable[[_TEMA001]:[_TEMA001]] is exactly the same as rawdatatable[_TEMA001] unless you intend to copy the formula across the sheet, in which case the former behaves as an absolute reference.

Those are the least of your problems though.

 

[Kennybaby93]

Thanks for the help Peter. But like previously stated. I can't reformat the table like stated. What i need is like 2 index match. That matches my criteria of what animal and year and kg The other has the same animal year but number. This would then have to return as 2 arrays then divide the arrays with the unitid matching so the correct id is getting divided by the correct one as this is critical to the outcome. Have this return as an array then do a max and min on that array. Which would work. I just have to find a way to code it. I have done one index match But i hacent quite got to to match the whole range correctly just yet.

 

[Peter Bartholomew]

The attached was uploaded from my laptop that has Office 365. It contains some exploration of formula-techniques to give you a better starting point for further nested aggregation (SUM, MAX, LARGE, AGGREGATE etc.).

 

[Kennybaby93]

thank you, I will have a look at it,

 

[Kennybaby93]

its done exactly the thing I said I cant do and that is reformat the table so that is nul and void.

 

[Peter Bartholomew]

OK. Sometimes in Excel you need helper cells. I avoid it to a great extent by using array formulas throughout (SUMPRODUCT and AGGREGATE do the same job but hide the fact that they are array formulas). At the moment I do not fully understand the problem you have posed, so it may be possible.

Then again you might simply be wasting your time working under so many constraints that any decent solution has already been ruled out, though I hope that is not the case. Good luck anyway.