Search results

Yes! That's what I asked for! Add extra columns in the sample foile with your manually calculated expected results (NOT non-working formulae).

You have formulae in place, so I don't know what you want assistance with - sorry. What is wrong with your current formulae? Highlight where you preceive that there are errors and tell us the result that you are expecting instead.

In the attached I have broken the formula down to show what each bit of it does. Please read the information carefully and ask if anything isn't clear.

The problem is, though, that the solution you found doesn't work fully - which is why you are asking here. I did spend some time trying to make it work for you, but could not, which is why I offered the more complex and newer approach, which I am happy to explain to you if you wish. Take this...

You forgot to format the column as TIME! If this isn't what you want, then you need to provide your expected results.

Do you want an explanation so that you can learn, or are you just going to dismiss this solution because you don't want to be bothered with learning new tricks? If the latter, then I'll step aside and leave it to someone else. The solution matches the rows, but there is no need to match columns...

In AF2 copied down: =IF(B2<A2,(1-MAX(22/24,A2))+MIN(6/24,B2),0) You can work out NDOT yourself from this, as I am not sure what you want exactly.

Your question is too vague to answer fully. It might be possible with VBA (I can't help you with this), but it's not at all clear what you are expecting to happen. Provide a sample workbook with before and after mocked up.

Further, here's a scalable version: =DROP(REDUCE("",B3:B10,LAMBDA(x,y,VSTACK(x,BYCOL(FILTER(VSTACK(Start:Finish!C4:N500),(VSTACK(Start:Finish!A4:A500)=y)*(VSTACK(Start:Finish!B4:B500)="USD")),LAMBDA(c,SUM(c)))))),1) I added two empty sheets: Start and Finish. Make sure that ALL source sheets...

Are you using 365? If so, try removing ALL expected results from the months columns of the grid and paste this into C3 followed by ENTER...

You can't control text formatting with a formula.

Yes, my post has been ignored, so I'll repeat my request. Can you share a desensitised workbook? Workbook, NOT screenshot. Thanks.

Can you share a desensitised workbook?

I don't think you really understood my point. I am very well aware of the German 'z' (you know very well that I am fluent in German). I can see nothing offensive here, intended or otherwise. I am pretty certain that this is the member's real name. My reaction has nothing to do with leniency or...

NAZI is only a part of the user name. It is quite possible that this member has a real name that begins with Naz and is habitually shortened to Nazie. Furthermore, the 'z' may not be pronounced 'ts', but as an Anglicised 'z'. I think your reaction is most probably over-sensitive.

You're welcome. :)

By the way, I am not a Sir - do I really look like a man??? Try this: =IFERROR(FILTER(Data!$I$4:$J$10001,(LEFT(Data!$A$3:$A$10000,20)=LEFT(C18,20))*(Data!$G$4:$G$10001=B18)),"")

Please provide a desensitised sample workbook. Which version of Excel is this for?

Any chance you could explain your requirement in plain English? What happened when you tried to adapt the formula I gave you? Please don't drip feed your requirements.

Can't answer that. then? ;) In E18 copied down: =IFERROR(FILTER(Data!$I$4:$J$10001,(Data!$A$3:$A$10000=C18)*(Data!$G$4:$G$10001=B18)),"")

And your answer to the other question?

Why? In what way? Which version of Excel?

They could do, but it would require a rethink of the results layout. I have set the formula to use the first of the two. (TAKE). At no point has the OP made it clear that both results should be shown (they have been very unwilling to properly clarify the expected results throughout, which has...

The formula doesn't use a named range - the ranges are defined within the formula. It's not a half measure. :confused: Glad to have helped. :)

It's like this: =LET(reg,VSTACK(start:finish!K2:K54050), date,VSTACK(start:finish!M2:M54050), sum_insured,VSTACK(start:finish!U2:U54050), DROP(REDUCE("",TOCOL(A3:A54050,1),LAMBDA(x,y, VSTACK(x,TAKE(FILTER(HSTACK(TEXT(date,"Mmmyyyy"),sum_insured),reg=y,"Not Found"),1)))),1)) What is hard about...