Need help with IF Statement

Copper Contributor

I'm new to creating IF, AND, OR Statements and need help.  The form pulls information from another tab within the workbook based on contract amounts:

1. If the contract amount > $100k OR If the previous contract amount was over $100k and is now being increased by 25% in value = Signatures 1, 2, 3 required - here's what I've come up with so far:

=IF('Page 1'!D30>100000,"{{ $E }}","")

2. If the previous contract amount was over $100k and another $100k OR another $250k is being added = Signatures 4, 5, 6 required - below is what I've come up with:

=IF('Page 1'!F21+'Page 1'!H21+'Page 1'!J21+'Page 1'!L21+'Page 1'!N21+'Page 1'!F27+'Page 1'!H27+'Page 1'!J27+'Page 1'!L27+'Page 1'!N27>=100000,"{{ $G }}",IF('Page 1'!F21+'Page 1'!H21+'Page 1'!J21+'Page 1'!L21+'Page 1'!N21+'Page 1'!F27+'Page 1'!H27+'Page 1'!J27+'Page 1'!L27+'Page 1'!N27>=250000,"{{ $G }}",""))

3. The form will also include text tagging for Adobe Sign.

 

Any help would be very much appreciated!

8 Replies

Hi @JMarini 

 

Without knowing what those values are in the referenced pages, no one would dare help you. It would probably be best if you shared the amounts to those referenced pages, i'm sure the numbers are not proprietary.

 

Cheers

@Yea_So  thank you for the reply; as I mentioned, I'm new to the formulas and these forums so was unsure the best way to present my question.  I have attached a copy of the form with the section I am trying to do the formulas for based on the four different scenarios listed.  Hopefully this makes better sense?

Hi @JMarini 

That 25% is based on the change order amount relative to the original contract amount, yes?

Hi @Yea_So - yes, that is correct.

Hi @JMarini 

Ok thank you.

The >100 or 25%, what is the protocol if the original contract is <100 but the change order is 25% or more, its not addressed in the matrix

If the original contract is <100 and goes over 100, then PEX is signer 2, Sub is 3, PM is 4 and SSR is 5. The 25% rule is only if the contract was originally over 100 and the new price is <250.
Ah hence the Or not the And. Ok

Hi @JMarini 

Here is the file : IF-AND-OR STMT helpFinal 

cheers

Here it is:

Yea_So_0-1622242375803.png

The ControlPage is where you change the tolerances:

Yea_So_1-1622242637651.png