# Search:

Page 1 of 3 1

1. ## Thread: SUM() excluding errors

Replies
11
Views
563

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

Martin

If you have Excel 2010>

=AGGREGATE(9,6,[range])

Ignores errors and text!

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

Replies
11
Views
563

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

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

I see you have moved from Fukuoka! :)

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

Replies
5
Views
486

### 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"})
...
4. ## Thread: convert id number into date

Replies
5
Views
486

### 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))
5. ## Thread: finish date formula needed

Replies
3
Views
394

### Another version using EDATE ...

Another version using EDATE

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

Replies
9
Views
1,136

### 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.
...
7. ## Thread: COUNTIF but if Hi-Lited Don't

Replies
9
Views
1,136

### 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.
8. ## Thread: Find last value in a column

Replies
15
Views
584

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

Hi zeddy

Strange that, as both files work for me!

Kevin

Replies
16
Views
1,033

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

@ HowardC

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

Replies
15
Views
584

### Hi zeddy See the attached file. Kevin

Hi zeddy

See the attached file.

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

Replies
15
Views
584

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

Hi lizat

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

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

Replies
3
Views
285

### 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...
13. ## Thread: Help with a solution for calculating incentives

Replies
8
Views
675

### 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:
...
14. ## Thread: Calculating Time from Custom date formats

Replies
3
Views
431

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

Hi bnorthby

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

Replies
12
Views
771

### 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
771

Hi zeddy

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

Replies
12
Views
771

### 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.
18. ## Thread: Wrap Text function treats negative sign like a hyphen

Replies
5
Views
802

### 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
19. ## Thread: conditional formatting to display a currency symbol based on text

Replies
9
Views
1,142

### 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
20. ## Thread: conditional formatting to display a currency symbol based on text

Replies
9
Views
1,142

### 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 :...
21. ## Thread: Function other than VLookup

Replies
10
Views
551

### Hi ab2537 I forgot to add to post #7 that it...

Hi ab2537

I forgot to add to post #7 that it is an Array formula. You need to CTRL + SHIFT + ENTER to enter the formula, not just enter, then copy across and down.

Kevin
22. ## Thread: Function other than VLookup

Replies
10
Views
551

### Hi I do not quite follow the meaning of...

Hi

I do not quite follow the meaning of 2316.38 as Martin pointed out. Are you trying to return all the value per the lookup! See the attached, might be of some help!
23. ## Thread: Dynamically Challenged (in Charts)

Replies
4
Views
377

### Hi brewer39 Would this be the site. ...

Hi brewer39

Would this be the site.

http://www.contextures.com/index.html
24. ## Thread: conditional formatting (more than 3 variables)

Replies
21
Views
1,814

### @ Maudibe I have read the OP post 3 times now,...

@ Maudibe

I have read the OP post 3 times now, Where does the data validation come in!!!!!
And why post the same formula ="Name"!!

@ brewer39
If you have Excel 2003< you will have to go the...
25. ## Thread: conditional formatting (more than 3 variables)

Replies
21
Views
1,814

### Hi New Lounger For Excel 2007> Assuming the...

Hi New Lounger

For Excel 2007>
Assuming the names will be in column A for example.
Select the cells that the rule needs to apply to. Home Tab > Conditional formatting > New Rule > Use a formula...
Results 1 to 25 of 51
Page 1 of 3 1