200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
I have the following problem that I cannot solve.
I would like to calculate averages of the item price per project excluding the highest value. As soon as the highest value is found, the cell should be colored.
In the file 'Testfile.xlsx' I performed this action manually. Running it manually is time consuming as it involves over 100,000 lines with over 1,500 items.
Who can help me with this?
The formula below will draw the average, disregarding the highest value in the range. Enter it in row 2 and copy down from there.
=(SUM(OFFSET(A2,0,5,COUNTIF(A:A,A2),1))-MAX(OFFSET(A2,0,5,COUNTIF(A:A,A2),1))) / (COUNTIF(A:A,A2)-1)I can't give you a formula to do the highlighting. It should be possible using CF but I didn't find the way. The formula below extracts the maximum on the worksheet. (It's part of the above formula.)
=MAX(OFFSET(A2,0,5,COUNTIF(A:A,A2),1))You can use a variation of the same formula in CF.
=F2=MAX(OFFSET(A2,0,5,COUNTIF(A:A,A2),1))You can apply this formula to F2:F6 and it works well. Then I applied a variation of that to F7:F12.
=F7=MAX(OFFSET(A7,0,5,COUNTIF(A:A,A7),1))That works well, too. But when one formula is applied to different product ranges, as is possible on the worksheet, it fails.
Thank you very much for this solution.
With the formula = MAX (OFFSET (A2,0,5, COUNTIF (A: A, A2), 1)) it almost works. However, the maximum amount must be calculated on the combination of the value in column A + column B. Both columns are text fields.
How can your formula be adjusted to take into account the data in BOTH columns when calculating the maximum amount?
To clarify my question I place file 'Testfile 2' where I have indicated with color the series to be calculated and with a yellow color the highest value in this series.
Thanks in advance for your answer.