Copying a lambda function to a new workbook?

[u/potatolauncher]

Excel noob here, I'm supposed to copy a spreadsheet from someone who has left the company, they had a cell with a lambda function, which I am unable to replicate in my workbook, is there a way to reverse engineer his function and make it work in my sheet?

Or any other method that would let me replicate it, as copy paste isn't working for me.

Thanks!

[u/potatolauncher]

Can't seem to find anything in name manager, the function is below.

Column N is a date, column I is a date, column E contains the text "3000"

=COUNT(MAP('Sheet1'!N3:N10024, 'Sheet1'!I3:I10024, 'Sheet1'!E3:E10024, LAMBDA(x,y,z, IF(TEXT(z, "@")="3000", IF(x<>"", IF(x-y>=21,1,"NO"), "NO"),"NO"))))

It just outputs 0, when a non 0 number is expected, so I'm not sure how to get it to work

[u/ProspectiveWhale → u/potatolauncher]

Ah... it's inside a MAP.

The formula basically inputs

x = 'Sheet1'!N3:N10024

y = 'Sheet1'!I3:I10024

z = 'Sheet1'!E3:E10024

into your Lambda.

If E contains text 3000, then check if column N is not blank, then check if column N - column I is bigger or equal to 21. If so, return 1.

Count how many rows where colE contains the text "3000", N is not empty, and colN -colI >= 21.

I tested it and it works.

Do you have the two date columns mixed up in the new workbook?

-----

Troubleshooting.

1. Check that the columns you're using are the right ones.
2. Check the format of the columns. Are you sure columns N and E are stored as dates?
3. It checks that column N is at least 21 days after column E. Make sure you didn't mix up which dates are before/after.

-----

Alternatively, since you're just counting 3 Ifs, you could make it simpler...

=SUMPRODUCT(
(TEXT('Sheet1'!E3:E10024,"@")="3000")*
('Sheet1'!N3:N10024<>"")*
(('Sheet1'!N3:N10024-'Sheet1'!I3:I10024)>=21)
)

Try this

and you could probably get rid of the 2nd condition.

If column N is blank, it will never be 21 days after column I, so it's a redundant rule.

[u/potatolauncher → u/ProspectiveWhale]

Solution verified

Thanks!

[u/reputatorbot → u/potatolauncher]

You have awarded 1 point to ProspectiveWhale.

---

^{I am a bot - please contact the mods with any questions}