# Search:

Type: Posts; User: a massive zebra; Keyword(s):

Page 1 of 4 1

1. ## Thread: How write formula contains words

by a massive zebra
Replies
2
Views
253

### Re: How write formula contains words

Perhaps:

=IF(SUMPRODUCT(--ISNUMBER(SEARCH(KeywordTable,E2))),"Alert","Batch")

Where KeywordTable is a named range containing all keywords.
2. ## Thread: Invisible Functions

by a massive zebra
Replies
0
Views
121

### Invisible Functions

I understand the DATEDIF function can be used in excel even though it does not appear on the list of inbuilt functions. Are any other functions available in excel that do not appear in the formula...
3. ## Thread: Look Up Help Required with newest date

by a massive zebra
Replies
10
Views
498

### Re: Look Up Help Required with newest date

Try this:

=VLOOKUP(MAX(IF(Sheet2!\$G\$2:\$G\$25="Priory",Sheet2!\$C\$2:\$C\$25))&"Priory",CHOOSE({1,2},Sheet2!\$C\$2:\$C\$25&Sheet2!\$G\$2:\$G\$25,Sheet2!\$H\$2:\$H\$25),2,0)

Enter with CTRL+SHFT+ENTR, not just...
4. ## Thread: Drag down same value at multiple points in a column

by a massive zebra
Replies
1
Views
190

### Re: Drag down same value at multiple points in a column

You could enter this formula into cell J2 and copy down across to column L and down the to the final row of data:

=IF(\$E2=\$E1,J1,B2)
5. ## Thread: return a number in place of a text string

by a massive zebra
Replies
7
Views
290

### Re: return a number in place of a text string

Hi Aladin,

Is there any benefit to using +0 rather than the VALUE function?

Thanks
6. ## Thread: return a number in place of a text string

by a massive zebra
Replies
7
Views
290

### Re: return a number in place of a text string

Replace your formula with:

=VALUE(20&LEFT(A1,2))

or

=(20&LEFT(A1,2))*1
7. ## Thread: Max Value IF

by a massive zebra
Replies
5
Views
218

### Re: Max Value IF

This also seems to work:

=SUMPRODUCT(MAX((A\$2:A\$2001=A2)*B\$2:B\$2001))

It is arguably a superior formula as it can be entered normally and probably calculates slightly faster.
8. ## Thread: Max Value IF

by a massive zebra
Replies
5
Views
218

### Re: Max Value IF

Try this, entered into cell C2 and copied down:

=MAX(IF(\$A\$2:\$A\$2001=A2,\$B\$2:\$B\$2001))

Enter with CTRL+SHTF+ENTR, not just enter.
9. ## Thread: Formula to Count Column items until next blank cell

by a massive zebra
Replies
5
Views
446

### Re: Formula to Count Column items until next blank cell

Glad to help. :-)
10. ## Thread: Formula to Count Column items until next blank cell

by a massive zebra
Replies
5
Views
446

### Re: Formula to Count Column items until next blank cell

Try this:

=COUNTIF(INDIRECT(ADDRESS(ROW()+1,3)&":"&ADDRESS(MIN(IF(ISBLANK(C2:C200),ROW(C2:C200))),3)),"Y")

Entered with CTRL+SHFT+ENTR
11. ## Thread: Formula to Count Column items until next blank cell

by a massive zebra
Replies
5
Views
446

### Re: Formula to Count Column items until next blank cell

Replace: =COUNTA(INDIRECT(C2&:&ADDRESS(MATCH(TRUE,INDEX((A3:A200<>0),0),0),3),FALSE),"Y")

with: =ADDRESS(MIN(IF(ISBLANK(C2:C200),ROW(C1:C200))),3)

Entered with CTRL+SHFT+ENTR, not just enter.
...
12. ## Thread: SUMPRODUCT with VLOOKUP

by a massive zebra
Replies
2
Views
303

### Re: SUMPRODUCT with VLOOKUP

Try this:

=SUMPRODUCT(IF(A2:A4={"Item1","Item2","Item3"},B2:B4),IF(A2:A4={"Item1","Item2","Item3"},C2:C4))/SUM(SUMIF(A2:A4,{"Item1","Item2","Item3"},B2:B4))

Entered with CTRL+SHFT+ENTR, not...
13. ## Thread: Averaging or Counting a number of cells in a column

by a massive zebra
Replies
4
Views
334

### Re: Averaging or Counting a number of cells in a column

Rick,

This formula could be copied down:

=AVERAGE(INDIRECT(ADDRESS(ROW(),LARGE(IF(A2:Y2<>"",COLUMN(A2:Y2)),8))&":"&ADDRESS(2,COLUMN()-1)))
14. ## Thread: Averaging or Counting a number of cells in a column

by a massive zebra
Replies
4
Views
334

### Re: Averaging or Counting a number of cells in a column

Wouldn't this be simpler?

=AVERAGE(INDIRECT(ADDRESS(ROW(),LARGE(IF(A2:Y2<>"",COLUMN(A2:Y2)),8))&":Y2"))

Entered into cell Z2 using CTRL+SHFT+ENTR, not just enter.
15. ## Thread: Check if data is in lower case or non-proper case

by a massive zebra
Replies
6
Views
294

### Re: Check if data is in lower case or non-proper case

Enter into C5 and copy down:

=IF(OR(LEFT(B5,6)="_rfsti",LEFT(B5,6)="_rfeti"),IF(ISERROR(FIND(MID(B5,2,1),LOWER(MID(B5,2,1)))),"","First letter should be...
16. ## Thread: SUMPRODUCT with non continuous ranges

by a massive zebra
Replies
7
Views
855

### Re: SUMPRODUCT with non continuous ranges

Great, that worked perfectly.

Thanks Aladin.
17. ## Thread: SUMPRODUCT with non continuous ranges

by a massive zebra
Replies
7
Views
855

### SUMPRODUCT with non continuous ranges

Hi,

I would like to multiply the values in cells F22, F39 and F48 by the corresponding values in column J. I thought the following formula should work, but it is not being accepted by excel.
...
18. ## Thread: ModelOff 2014 - starts 25th October!

by a massive zebra
Replies
1
Views
30,996

### Re: ModelOff 2014 - starts 25th October!

I've qualified for round 2! :)

Is round 2 the final round before the world finals? Or is there a round 3 and/or national finals?

Also, I ran out of time before submitting my workbook for the...
19. ## Thread: Countifs / sumproduct...

by a massive zebra
Replies
9
Views
224

### Re: Countifs / sumproduct...

Thanks very much for the explanation. :)
20. ## Thread: Is there anything faster than countif?

by a massive zebra
Replies
3
Views
1,164

### Re: Is there anything faster than countif?

COUNTIF is a quick function. I would guess you must have a lot of other formulae in the spreadsheet. Try calculating that sheet only: Formulas > Calculation > Calculate Sheet
21. ## Thread: Countifs / sumproduct...

by a massive zebra
Replies
9
Views
224

### Re: Countifs / sumproduct...

Hi Aladin. Can you explain why the +0 is necessary? Is it to ensure the data is recognised as a number? If so why not use the VALUE function? Always appreciate your input. Thanks.
22. ## Thread: SUM with Critera in 3 columns.

by a massive zebra
Replies
5
Views
145

### Re: SUM with Critera in 3 columns.

If you are using excel 2007 or later, you could just use COUNTIFS: =COUNTIFS('TASK DETAILS'!D1:D5000,2014,'TASK DETAILS'!E1:E5000,"Jan",'TASK DETAILS'!AT1:AT5000,"Y")
23. ## Thread: SUM with Critera in 3 columns.

by a massive zebra
Replies
5
Views
145

### Re: SUM with Critera in 3 columns.

I think you need consistent row ranges, so either 1 to 1000 or 1 to 5000, but not a mixture of both. If that doesn't work, does this: =SUMPRODUCT(('TASK DETAILS'!D1:D5000="2014")*('TASK...
24. ## Thread: Numbers only

by a massive zebra
Replies
4
Views
144

### Re: Numbers only

In E2: =IF(ISNUMBER(B2),B2,IF(ISNUMBER(C2),C2,D2))
25. ## Thread: Backwards compatibility to 97-03 for IFERROR array

by a massive zebra
Replies
6
Views
498

### Re: Backwards compatibility to 97-03 for IFERROR array

=IF(ISERROR(INDEX('Player Seasons'!\$E:\$E,SMALL(IF(FREQUENCY(IF('Player Seasons'!\$D\$2:\$D\$358="REG",MATCH('Player Seasons'!\$E\$2:\$E\$358,'Player Seasons'!\$E\$2:\$E\$358,0)),ROW('Player...
Results 1 to 25 of 87
Page 1 of 4 1