Need to find max value in range, multiply that by a number then add remaining cells

[MikeBrin]

Good day,

I'm trying to find a max value in a range of 5 cells (e.g. C10: C14).
I want to find the max value in that range, then multiply that value by an integer, located in B1. Then using that result, add the remaining values (except the MAX value) in the range to that result and put it in B2.

Any assistance would be appreciated,
Mike

[wk9128]

Hi MikeBrin Welcome to the forum

Please Login or Register  to view this content.

Please read and refer to this link, how to upload attachments,follow mathod 2
https://www.excelforum.com/the-water...his-forum.html


Post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information
(10-20 rows, not thousands...) and some manually calculated results. Just before posting, scroll down to GO ADVANCED, click,
and then scroll down to MANAGE ATTACHMENTS and click again. Now follow the instructions at the top of that screen

[Gregb11]

In cell B2, try:

=MAX(C10:C14)*B1+SUM(C10:C14)-MAX(C10:C14)

Or rearranged for LET:
=LET(
rng,C10:C14,
mx,MAX(rng),
mx*B1+SUM(rng)-mx)