Hi all



I have a list of over 10k lines that include multiple groups. Within these groups are the columns "MEMBER", "TYPES", and "AMOUNTS". The TYPE is either a TC or 26. Regardless how many lines are within a single group, all amounts that are less than the highest amount has a discount applied to it based on the TYPE. TC types have a 50% discount applied to each amount that are less than the highest amount while type 26 has a 25% discount applied to them. Is there a formula that would allow me to do that? I am attaching a file with an example of what my intentions are.

Thanks

Attached Files

Example.xlsx (10.6 KB, 13 views)

Paste in D2 and drag down column :

See https://exceljet.net/formula/max-if-criteria-match

Try this array formula in E2 to get the discount
confirmed with Shift+Ctrl+Enter not just Enter
and drag down

Attached Files

wheddingsjr_Example.xlsx (10.5 KB, 9 views)

Thanks NoSparks

This was VERY helpful. Unfortunately though, the highest number should not calculate and populate, its only those that are less than it. I am not sure what you mean by "confirmed with Shift+Ctrl+Enter not just Enter and drag down". I thought perhaps thats why the high number is being populated because when I do the "Shift+Ctrl+Enter not just Enter nothing happens.

change the last 0 in the formula to ""

as you can see from the link included the MAX(IF portion of the formula is an array formula
when you enter a 'normal' formula into a cell you just hit Enter for Excel to accept it
with an array formula you have to hit Shift+Ctrl+Enter all at the same time for Excel to accept it as an array formula
and Excel will automatically add the { and } to the formula.

drag down... click and hold on that little square in the lower right corner of the cell and pull it down the column to copy the formula to those cells


Hope that helps.

Thanks NoSparks, that did the trick and it work out perfectly. I truley appreciate all of your help