# 3 condition IF formula with a MAX formula



## Alex D (Dec 21, 2022)

My current formula is this:   =MAX(IF(D3>315,D3*0.9,D3*0.95),245)


Agent NameEmails Handled*D3*SupervisorMain RegionUS or indiaMain PODUs or India*J3**K3*John Doe25221Team DoeNorthUS NorthNorth PodUS North Pod245245

What I am trying to do to my current formula (in K3), is add "IF D3 is greater than J3, then give me J3"...without changing anything else in the current formula.


----------



## Fluff (Dec 21, 2022)

How about
	
	
	
	
	
	



```
=IF(D3>J3,J3,MAX(IF(D3>315,D3*0.9,D3*0.95),245))
```


----------



## jdellasala (Dec 21, 2022)

Please post using *XL2BB* so we can see WHERE the formula is, and what 3 things you're trying to IF. Have you tried *IFS*?


----------



## Fluff (Dec 21, 2022)

jdellasala said:


> Have you tried *IFS*?


That is a useless function & is far less efficient than a nested IF formula.
Also what difference does it make the formula is?


----------



## jdellasala (Dec 21, 2022)

Fluff said:


> That is a useless function & is far less efficient than a nested IF formula.
> Also what difference does it make the formula is?


I don't think MS would waste the time and $ to develop a new "useless" function. I've found it much easier to use in many situations. I've never been fond of having to nest IF statements though.


----------



## Fluff (Dec 21, 2022)

The fact that you find it easier to use, does not change the fact it is far less efficient than a nested If which can do exactly the same thing.


----------



## jdellasala (Dec 21, 2022)

Fluff said:


> The fact that you find it easier to use, does not change the fact it is far less efficient than a nested If which can do exactly the same thing.


You've said that before, but I find it difficult to believe that calling one function multiple times is somehow more efficient subjectively without timing data. Objectively, its efficiency is a personal preference.


----------



## Fluff (Dec 21, 2022)

jdellasala said:


> Objectively, its efficiency is a personal preference.


Nope, it's a fact, IFS will evaluate absolutely everything which a nested IF will not do. You can see that use the Evaluate Formula feature on the formula tab.


----------



## jdellasala (Dec 21, 2022)

Fluff said:


> Nope, it's a fact, IFS will evaluate absolutely everything which a nested IF will not do. You can see that use the Evaluate Formula feature on the formula tab.


Show the data. Right now it's all talk.


----------



## Fluff (Dec 21, 2022)

If you don't believe what I am saying, I have already told you how to verify it.


----------



## Alex D (Dec 21, 2022)

My current formula is this:   =MAX(IF(D3>315,D3*0.9,D3*0.95),245)


Agent NameEmails Handled*D3*SupervisorMain RegionUS or indiaMain PODUs or India*J3**K3*John Doe25221Team DoeNorthUS NorthNorth PodUS North Pod245245

What I am trying to do to my current formula (in K3), is add "IF D3 is greater than J3, then give me J3"...without changing anything else in the current formula.


----------



## jdellasala (Dec 21, 2022)

Fluff said:


> If you don't believe what I am saying, I have already told you how to verify it.


If I recall, it didn't include timings of the same IFS formula compared to the same nested IF formula. It was postulation. Let's see some timings on 100,000 or more iterations.


----------



## Fluff (Dec 21, 2022)

I never said anything about timings, try reading what I actually posted.
If you cannot be bother to try what I suggested, that's fine. But you will not learn anything.


----------



## jdellasala (Dec 21, 2022)

Fluff said:


> I never said anything about timings, try reading what I actually posted.
> If you cannot be bother to try what I suggested, that's fine. But you will not learn anything.


UNCLE! I did some timings, and found that 300,00 IFS statements took a little over 0.6 seconds, and the comparable IF statements took a little over 0.3 seconds. Very surprised. Don't remember what you suggested, but I've proven it for myself which is probably best anyway. Thanks!


----------



## Fluff (Dec 21, 2022)

For that number of cells the time difference is pretty insignificant, although if the True portion contained a complex formula then depending on the number of criteria it could really start to slow down quite a lot.


----------

