IF/AND/THEN Formula

It's possible that the data entered will look like this: (see attached)

No, those are the only variables.

IF G1,2,3 is < or = -151 then treat as <-151. So, -150 or higher or -151 or fewer (only whole numbers used in G).

It is a sample table only and J1 is target. There is no other data involved.

That formula is very close, but when I copy/paste it to actual file it doesn't work for all variables. See attached. Non highlighted values are correct, yellow highlighted values are incorrect in column "J" and the expected value is in column "K".

The full criteria is listed in the original question. In order to find a formula the works just change the values E1,E2,E3,F1,F2,F3,G1,G2,& G3.

So, in "3 EXAMPLE" if you change the value of G1 to "-150" then J1 should be "100".

OR

If you change the value of F1 to "L", G1 to "-150", & G2 to "-190" then J1 would be "-150"

and so on.

I don't know how to do that, sorry.

Maybe this will help. I have a formula in column "L" that allows me to calculate for E2 & E3, all I'm looking to do is add E1. While the formula in "L" is different from what I'm looking for perhaps it can be used as a starting point?

Hi
I suppose I am thinking correctly, but ...

Try the following formula

=IF(E1=1,IF(F1="W",(G1>-151)*H1,IF(F2="W",SUMPRODUCT(--(G1:G2>-151),H1:H2,ROW($A$1:$A$2)),SUMPRODUCT(--(G1:G3>-151),H1:H3,ROW($A$1:$A$3)))),"")

Formula:

Please Login or Register  to view this content.

I think the value you put into J4 is not correct. All other values are according to the logical formula expressed.

If your problem is resolved mark it as "SOLVED".

See the file 3 EXAMPLE.xlsx

Regards

That is very close, J.A. J4's value was correct in the example. If you look at the original 18 sets of outcomes on 1st post you'll see J4 models after the 18th posted criteria (one at very bottom of page).

It looks like there are still some mistakes in your post. I haven't checked to see if the gaps in the criteria that others have mentioned are now covered, but referring to your duplicate thread...

Originally Posted by quibilty

I'm looking for the first occurrence of a W in the three rows. If a W occurs in F1 then only the data in row 1 is incorporated. If G1 is >=-150 then J1 = H1. If G1 <-150 then J1 = 0. I.E., examples 1 & 2

How do you explain conditions 3 and 4? They don't meet the criteria of 'first occurrence of W'

This is my best guess based on what you have provided for us to work with in both threads.

PHP Code: 

=CHOOSE(IFERROR(MATCH("W",F1:F3,0),3),
IF(G1<-150,0,H1),
CHOOSE(MATCH(--(--(G1<-150)&--(G2<-150)),{0,1,10,11},0),(H2*2)+H1,H1,H2,0),
CHOOSE(MATCH(--(--(G1<-150)&--(G2<-150)&--(G3<-150)),{0,1,10,11,100,101,110,111},0),(H3*3)+(H2*2)+H1,(H2*2)+H1,(H3*2)+H1,"IDK","DBA",H2,H3,0)) 


I've split the formula over 4 lines, the first line finds which of the 3 rows contains the first instance of 'W', defaulting to the 3rd row if there is no match. The remaining 3 rows hold the calculations to use based on which row holds the first 'W'.

There was no criteria specified for 'W' in F3, with G1>-150, G2<-150 and G3<-150, or with G1<-150, G2>-150 and G3>-150, if the formula detects either of these combinations then you will be advised of the uncertainty.