View Single Post
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
MyVeryOwnSelf[_3_] MyVeryOwnSelf[_3_] is offline
external usenet poster
 
Posts: 56
Default AVERAGE with multiple conditions

Hello, I'm trying to calculate an average, but need to adhere to a
couple conditions:
- Average must be of A and B
- A is capped at 125%
- B is capped at 163%
- A is capped at 100% if B<100%
- B is capped at 100% if A<100%

I came up with the following, but I'm just not sure how to make them
play nicely together:
=AVERAGE(IF(G16125%,125%,G16),(IF(M16163%,163%,M 16)))
=AVERAGE(IF(M17<1,1,G17),(IF(G17<1,1,M17)))


If I understand the requirements, this might help:
=AVERAGE(MIN(G16, 125%, IF(M16<1,1,G16)),
MIN(M16, 163%, IF(G16<1,1,M16)))