If formula in or paring another formula

[N0t Y0urs]

I have the current formula of

=(G40+D41)*inputs!$B$11 and it works perfectly but I want to add this functionality to it.

If G40>1000000 then G40+D41-1000000 if not (G40+D41)*inputs!$B$11

I can’t seem to make it work any assistance would be amazing

 

[N0t Y0urs]

I have the current formula of

=(G40+D41)*inputs!$B$11 and it works perfectly but I want to add this functionality to it.

If G40>1000000 then G40+D41-1000000 if not (G40+D41)*inputs!$B$11

I can’t seem to make it work any assistance would be amazing

I have amended my formula to the following
=ifs(G40+D41>1000,(G40+D41)*inputs!$B11,G40+D41>1000000,G40+D41-1000000)

It’s working all the way up to the second condition. It’s just subtracting the % from the first condition rather than ignoring it and subtracting anything over 1000000.

Do I need to change the >1000 to be a range of 1000-999999

 

[StephenCrump]

If G40+D41>1000000, then the first condition G40+D41>1000 will also be true, i.e. your IFS() will never reach the second condition.

You could simply reverse the order of the two conditions.

What should happen if G40+D41<=1000?

 

[N0t Y0urs]

If G40+D41>1000000, then the first condition G40+D41>1000 will also be true, i.e. your IFS() will never reach the second condition.

You could simply reverse the order of the two conditions.

What should happen if G40+D41<=1000?

Thanks but I just fine tuned it and in the process looked at my order.

My solution is

IFS(G40+D41-E41>1000000,G40+D41-E41-1000000,G40+D41-E41>1000,(G40+D41-E41)*inputs!$b$11,G40+D41-E41>1,0)

I did add the additional subtraction in after I found my error in the order I wrote the formula.

Thanks