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)))

�ang cần gấp
_________________
rau sạch

https://t.co/D99o6J5uAE

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)))

Hello! if you have multiple conditions, the best thing to utilize is the function called AVERAGEIFS. AVERAGEIFS average depending on the criterias you have inputted. So this makes it more convenient rather than using the average function in conjunction with the IF Function


By the way, I also learned a couple of things here about use Average in excel, it might help you too!

https://www.efinancialmodels.com/kno...rage-function/


Hope this helps!