if condition for finding actual negative hours in my data

[vjnvinod]

Hi All,

I am using below formula for finding negative in my data

Status=IF(AF507<0,"Negative","postive") and it finds correctly but see my data below
if there is reversal like below where one entry has been reversed (same GUI, engagement and Transaction date), i want to show my status column as "blank" or "offset" instead of Negative and postive

how do i modify my above formula to achieve that?


GUI Engagement ID Transaction Date Hours Status
16001294 E-65622690 4-Oct-20 -5 Negative
16001294 E-65622690 4-Oct-20 5 postive
16001294 E-65622690 5-Oct-20 -5 Negative
16001294 E-65622690 5-Oct-20 5 postive
16001294 E-41069102 6-Oct-20 5 postive
16001294 E-65622690 6-Oct-20 -5 Negative

[Glenn Kennedy]

Maybe this in F5, copied down:

=IF(ISNUMBER(MATCH(1,(A4:A8=A3)*(B4:B8=B3)*(C4:C8=C3)*(D4:D8=-D3),0)),"Offset","")

[Glenn Kennedy]

You seem to be using an older version of Excel than me. So, please refer to the file (previous post). If the formulae are enclosed within a pair of { }, these are array formulae.

These are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see the curly brackets { } appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

[vjnvinod]

Hi Glenn,

Its actually returning blank in my excel, see below screenshot, not sure what i am doing wrong

Attachment 707899

[Glenn Kennedy]

Did you open my file?
Did it work there?
Were there {} around the formula?

[vjnvinod]

Did you open my file?
Yes i did open your file
Did it work there?
yes it did work there
Were there {} around the formula?
yes i can see that

Its not working for me is it because i am using xlsm format? sorry about my Excel knowledge just a beginner

[Glenn Kennedy]

No. .xlsm will make no difference.

Did you set the formulae in YOUR sheet, using CTRL-SHIFT-ENTER as described in Post #3?

If so, do you see the {} around the formula in YOUR sheet?

[vjnvinod]

Hi,

yes, when i use Ctrl shift Enter and then press Enter, i can see curly brackets there, but when i change the ranges and column, the curly bracket goes automatically

I changed the formula to below and curly bracket goes out, apparently my output comes as blank
=IF(ISNUMBER(MATCH(1,(B7:B13843=B6)*(H7:H13843=H6)*(AA7:AA13843=AA6)*(AE7:AE13843=-AE6),0)),"Offset","")

[Glenn Kennedy]

That's the way they work in older Excel versions. Go to the first cell with the formula. Adjust the ranges there. Enter using CTRL-SHIFT-ENTER. Check that the {} are there, drag the formula down the column.

[vjnvinod]

Unfortunately after many attempt, i am not able to bring the curly bracket
is there any other formula i can use to get my result?

[bebo021999]

Try alternative non-array formula:

Please Login or Register  to view this content.

If it does not meet requirement, try another

Please Login or Register  to view this content.

[vjnvinod]

Hi,

This does help

however just a follow up question on this
if i have to do this in power query, how will bring this logic?

currently what i have is

= Table.AddColumn(#"Changed Type with Locale1", "-/+", each if [Hours] < 0 then "Negative" else "Postive")

[vjnvinod]

Hi,

how do i modify this formula so that i can use it in a table?
i pasted the second option directly to my table and it actually didn't worked

[Glenn Kennedy]

Please post a file with the table.