mercredi 8 mai 2019

Is there a -more- efficient way to make excel return a value x if cell exceeds value y?

Sorry for maybe a lame posting, but I often encounter a need to return a pre-defined value x if cell exceeds y, but more efficient than below (due to often having long formulas)

Example below. I want cell to return 10 if

SUMIFS($AI$42:$AI$51;$AH$42:$AH$51;"<"&AH51)+1

is more than 10.

Is there a more efficient/elegant way than repeating this twice? i.e.

If((SUMIFS($AI$42:$AI$51;$AH$42:$AH$51;"<"&AH51)+1)>10;10;SUMIFS($AI$42:$AI$51;$AH$42:$AH$51;"<"&AH51)+1)

Thanks in advance!

Aucun commentaire:

Enregistrer un commentaire