IF function with multiple criteria and values

[Rileyb1]

Hello,
I need to do this:
If the value for a cell in each row is less than the mid-point value for the row and column, display -1
If the value for a cell in each row is greater than the mid-point value for the row and column display 1, otherwise display 0


It needs to account for a value in both the same row and column. I tried this: =IF(AND(A1<A$24,A1<$W1),AA3,IF(AND(A1>A$24,A1>$W1),$AA$2,$AA$4)) but all I got where 1's


Can anyone help?

[:) Sixthsense :)]

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[Glenn Kennedy]

I think you need to give us some more information. like a sample sheet with expected results. I don't know what answers you are expecting... but the formula seems to be doing what you are asking it to do...

[Rileyb1]

OK so at the bottom where there are four ones the fourth should be -1 as it is less than w1 and d24 but it shows as one, all of them do.

I hope this is enough for you to help if not just let me know.

Thanks

[Glenn Kennedy]

Should AA3 be locked??? $AA$3...

[Glenn Kennedy]

Yes, that's your problem. AA3 was moving with your formula. Lock it down!

[Rileyb1]

ok AA3 is locked down and still all 1's

[Glenn Kennedy]

Is it?? See the attached file.

[Rileyb1]

1 and -1 work but no 0

[Glenn Kennedy]

It returns zer when the criteria are met. Copy it over to column S. That is th eonly time, with row A's data where it happens.

s1 = 0.9458. That's greater than s24 (0.5997), but less than w1 (.95281). So it doesn't return AA3.

S1 is greater than s24, but it is not greater than W1, so it does not return AA2.

therefore it does return AA4.

So, as I asid earlier; your formula IS doing what you have asked it to do. the question now is, have you asked it the right question?

So far you have just pasted a CSV with no formulas. What are the underlying formulas? Please post an Excel sheet not a CSV