# Search:

Page 1 of 3 1

1. ## Thread: Index and Match Problem

Replies
5
Views
174

### Hi HowardC I posted a bit to quick, try the...

Hi HowardC

I posted a bit to quick, try the following in K1 and copy down. Its a bit long winded! and is an array formula.
...
2. ## Thread: Index and Match Problem

Replies
5
Views
174

### Hi HowardC Try the following in K2 & copy...

Hi HowardC

Try the following in K2 & copy down.

=IFERROR(INDEX(\$C\$2:\$C\$9,MATCH(\$J\$2,\$B\$2:\$B\$9,0)+ROW()-2),"")
3. ## Thread: Help needed with a formula combining lookup and if statement - Excel 2010

Replies
4
Views
137

### Hi neil =SUMIF(B5:I5,">0",\$B\$3:\$I\$3)

Hi neil

=SUMIF(B5:I5,">0",\$B\$3:\$I\$3)
4. ## Thread: Help needed with a formula combining lookup and if statement - Excel 2010

Replies
4
Views
137

### Hi neil Perhaps the SUMIF, in K5 & copy down!...

Hi neil

Perhaps the SUMIF, in K5 & copy down!

=SUMIF(B5:I5,"<>N/A",\$B\$3:\$I\$3)
5. ## Thread: Calculating time difference in military time

Replies
14
Views
884

### Hi Or maybe a formula if you do not want to...

Hi

Or maybe a formula if you do not want to use code.

=MOD((--TEXT(B2,"00\:00\:00"))-(--TEXT(A2,"00\:00\:00")),1) copy down and format as "hh:mm:ss or [h]:mm:ss".
6. ## Thread: SUM() excluding errors

Replies
11
Views
2,383

### Martin If you have Excel 2010> ...

Martin

If you have Excel 2010>

=AGGREGATE(9,6,[range])

Ignores errors and text!

Elegant. :)
7. ## Thread: SUM() excluding errors

Replies
11
Views
2,383

### Or =SUMIF([range],">0") I see you have moved...

Or =SUMIF([range],">0")

I see you have moved from Fukuoka! :)

Kevin
8. ## Thread: convert id number into date

Replies
5
Views
1,577

### Hi Perhaps something along these lines. In...

Hi

Perhaps something along these lines.

In B1: =DATE("19"&LEFT(A1,2),MID(A1,3,2),MID(A1,5,2)) & format as dd mmmm yyy
In C1: =LOOKUP(RIGHT(A1,1)+0,{1,2,3},{"johor","kedah","kelantan"})
...
9. ## Thread: convert id number into date

Replies
5
Views
1,577

### Hi Assuming in A1: 801107, in B1 and format...

Hi

Assuming in A1: 801107, in B1 and format the cells "dd mmmm yyy", without the quotes.

=DATE("19"&LEFT(A1,2),MID(A1,3,2),RIGHT(A1,2))
10. ## Thread: finish date formula needed

Replies
3
Views
520

### Another version using EDATE ...

Another version using EDATE

=EDATE(B2,(A2*12)-1)
11. ## Thread: COUNTIF but if Hi-Lited Don't

Replies
9
Views
1,702

### I know you guys on here are not bothered about...

I know you guys on here are not bothered about cross posting!

But this guy (JOB-Dave) has even used the code in post #3 and not even given the credit to Maudibe.
...
12. ## Thread: COUNTIF but if Hi-Lited Don't

Replies
9
Views
1,702

### Hi JOB-Dave Is there conditional formatting...

Hi JOB-Dave

Is there conditional formatting involved! If so you can use that to achieve the count.
13. ## Thread: Find last value in a column

Replies
15
Views
1,020

### Hi zeddy Strange that, as both files work for...

Hi zeddy

Strange that, as both files work for me!

Kevin

Replies
16
Views
1,629

### @ HowardC Try cleaning your file up for...

@ HowardC

Try cleaning your file up for starters!!
15. ## Thread: Find last value in a column

Replies
15
Views
1,020

### Hi zeddy See the attached file. Kevin

Hi zeddy

See the attached file.

Kevin
16. ## Thread: Find last value in a column

Replies
15
Views
1,020

### Hi lizat Try the LOOKUP function:...

Hi lizat

Try the LOOKUP function:
=LOOKUP(2,1/(C:C<>""),C:C)

Kevin
17. ## Thread: Vlookup where numbers on one sheet are text

Replies
3
Views
435

### Hi HowardC Perhaps another way and perhaps...

Hi HowardC

Perhaps another way and perhaps shorter, is to convert the lookup number to text, I have also wrapped in a IFERROR as you uploaded a .xlxs file.

=IFERROR(VLOOKUP(C1&"",'Imported...
18. ## Thread: Help with a solution for calculating incentives

Replies
8
Views
911

### Hi Eagle01 SUMIFS is all you need for that in...

Hi Eagle01

SUMIFS is all you need for that in your file that you posted in post #1
In C6 and copy down:
...
19. ## Thread: Calculating Time from Custom date formats

Replies
3
Views
584

### Hi bnorthby Or =B4-A4 & format the cells as...

Hi bnorthby

Or =B4-A4 & format the cells as [m]
20. ## Thread: A lookup to take into account 2 column values and 1 header value

Replies
12
Views
1,142

### Hi Personally I think most people on the...

Hi

Personally I think most people on the forums don't have really huge files so speed, time etc is not a big problem, but I could be wrong.

@ zeddy. See the attached link with regards to...

Replies
12
Views
1,142

Hi zeddy

Thank you.
22. ## Thread: A lookup to take into account 2 column values and 1 header value

Replies
12
Views
1,142

### Hi stimpsond1 INDEX & MATCH should achieve the...

Hi stimpsond1

INDEX & MATCH should achieve the result you are after. See the attached file. The formula in D8 is an array formula and entered as CTRL + SHIFT + ENTER not just enter, then copy down.
23. ## Thread: Wrap Text function treats negative sign like a hyphen

Replies
5
Views
1,263

### Hi yoree Why don't you keep the Lat/Long in...

Hi yoree

Why don't you keep the Lat/Long in their own cells, at least they will be numbers and you can still use them. Adding "," etc they are then text!

Kevin
24. ## Thread: conditional formatting to display a currency symbol based on text

Replies
9
Views
2,695

### Hi Martin In Excel 2007 & 2010 you can. I have...

Hi Martin

In Excel 2007 & 2010 you can. I have noticed that you uploaded xls file, not possible in 2003!

Kevin
25. ## Thread: conditional formatting to display a currency symbol based on text

Replies
9
Views
2,695

### Hi stimpsond1 You could also use Conditional...

Hi stimpsond1

You could also use Conditional Formatting, select column B or range of cells in column B you wish to apply the rules.

For USD: =\$A1="USD" format as US dollars.
For GBP :...
Results 1 to 25 of 56
Page 1 of 3 1