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
252

Re: How write formula contains words

Perhaps:

Where KeywordTable is a named range containing all keywords.

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...

by a massive zebra
Replies
10
Views
497

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
289

Re: return a number in place of a text string

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
289

Re: return a number in place of a text string

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

or

=(20&LEFT(A1,2))*1

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.

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

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:

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

Entered with CTRL+SHFT+ENTR, not just enter.
...

by a massive zebra
Replies
2
Views
302

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:

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?

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
293

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
851

Re: SUMPRODUCT with non continuous ranges

Great, that worked perfectly.

17. Thread: SUMPRODUCT with non continuous ranges

by a massive zebra
Replies
7
Views
851

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,678

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...

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,163

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

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
144

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
144

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...

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
497

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