add <> condition to formula

Hi

I have a formula that works perfectly, however I need to add two conditions and I got a bit lost.
here is the formula
IF(P15>Q$13,SQRT(P15-P$13)+((F16*P15)/P$13),
((F16*P15)/P$13))/2.5
I've shown the two conditions on two lines for easier identification.
You can see there are two conditions
The extra two conditions I would like to add to both of the above two conditions
1)  IF(F60>50
2) AND(H16<1.1
Return zero if both conditions qualify. I want to apply to both of the conditions in the formula.
Please refer to attached sheet
Thanks
Ian
Add-2-conditions.xlsx
raceproretiredAsked:
Who is Participating?
 
Ejgil HedegaardCommented:
Use this
IF(AND(F60>50,H16<1.1),0,
IF(P15>Q$13,SQRT(P15-P$13)+((F16*P15)/P$13),
((F16*P15)/P$13))/2.5 )

F60 is empty, so I guess it should be F16 and not F60, and then it is
=IF(AND(F16>50,H16<1.1),0,IF(P15>Q$13,SQRT(P15-P$13)+((F16*P15)/P$13),((F16*P15)/P$13))/2.5)
Add-2-conditions.xlsx
1
 
raceproretiredAuthor Commented:
Hi Ejgil, it works fine thank you. Can I ask one more add on and that is either condition result should not exceed
a value of 1000. If result exceeds 1000 then 1000 otherwise etc. To be applied to both conditions
Thanks if you can help.
Ian
0
 
raceproretiredAuthor Commented:
please ignore last request and modify original formula below to give a max result of 1,000 for both conditions
Thanks

IF(P15>Q$13,SQRT(P15-P$13)+((F16*P15)/P$13),
((F16*P15)/P$13))/2.5
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
raceproretiredAuthor Commented:
This was my best effort but is wrong :(

=IF(IF(P15>Q$13,SQRT(P15-P$13)+((F16*P15)/P$13>1000,1000)),
IF(((F16*P15)/P$13>1000,1000,((F16*P15)/P$13))/2.5)

ps..... yes you were right about the F16 being correct cell... sorry
0
 
raceproretiredAuthor Commented:
No reply yet from my extra request which I may ask again on a separate question.
However the original question was answered successfully- Well Done Ejgil
and will close the question shortly and award you full points. Thanks
Ian
0
 
Ejgil HedegaardCommented:
Try this
=IF(AND(F16>50,H16<1.1),0,IF(P15>Q$13,IF(SQRT(P15-P$13)+((F16*P15)/P$13)>1000,1000,SQRT(P15-P$13)+((F16*P15)/P$13)),IF(F16*P15/P$13>1000,1000,F16*P15/P$13))/2.5)

Open in new window

1
 
raceproretiredAuthor Commented:
That's great, Thanks
0
 
raceproretiredAuthor Commented:
Thanks Ejgil, your expert solution was much appreciated.
HNY   Ian
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.