The Excel Questions Thread

[suited]



Blanking on this right now: I have a bunch of data and have calculated the days of the week (mon, tues, etc. ) how do I count each day of the week? Same thing if I have a bunch of products how do I count each specific product?

Meaning you just want to count the number of products?

=counta(insert cell range here)

Edit, nvrmind, I missed the word "specific" in your post. OTC's post would apply, then.

 

[rajesh06]

Click the arrow to expand "Number" under the Home ribbon. Under the Number tab look for "Special" and then Phone Number.

 

[vaulter1]



Click the arrow to expand "Number" under the Home ribbon. Under the Number tab look for "Special" and then Phone Number.

My understanding from the OP was that he didn't want to just change the formatting but had to compare phone numbers with 'harcoded' formatting. Though if it's just visual formatting then - easy answer.

 

[Reggs]



Not sure if I understand you correctly but the below formulas will take your phone number from 7145555555 to (714) 555-5555 and back again.

• This assumes that 7145555555 is in cell A1 =CONCATENATE("(",LEFT(A1,3),")"," ",MID(A1,4,3),"-",RIGHT(A1,4))
• This assumes that (714) 555-5555 is in cell A2 =CONCATENATE(MID(A2,2,3),MID(A2,7,3),RIGHT(A2,4))

The power of CONCATENATE is really quite useful

That's exactly what I was looking for, thanks so much!

 

[Sir Humphrey Appleby]

Is it possible to convert SQL code into Excel macros? I want to get

SELECT Employee_Name, Speed, Time
FROM "Hour"
GROUP BY Job
HAVING Time>0.5
ORDER BY Speed

Then select the slowest two people who are left in each job (sometimes there's only one).

If it was in Access I could do it all except for the last bit but I'm less good with Excel.

 

[rajesh06]

You should be able to handle something with this level of complexity with a pivot table

 

[Sir Humphrey Appleby]



You should be able to handle something with this level of complexity with a pivot table

Tried to have a go at pivot tables with another task but couldn't work out how to filter by greater than other than by clicking each time each person has had at that job (I'm doing this each hour and only want to count people doing it for more than half an hour). Then how can I get a pivot table to get the bottom two from each?

 

[rajesh06]

OK I see. Not sure I have a good solution for you then.
You may be able to do something using tables (excel 2007 and 2010), creating helper columns and working with filters.
Alternatively, I thought there may be a query facility that you could use with your SQL statements (but don't know this for certain.)

 

[Sir Humphrey Appleby]



OK I see. Not sure I have a good solution for you then.
You may be able to do something using tables (excel 2007 and 2010), creating helper columns and working with filters.
Alternatively, I thought there may be a query facility that you could use with your SQL statements (but don't know this for certain.)

There is but for some reason it doesn't work at work. I've got it to work in VBA now (I've had to delete a few columns but that's fine), now just trying to work out how to filter the time and speed (I want people performing under 50% of their target and are on task for half an hour or more) and then send the results via email.

Playing around with macros has made me so lazy that even sending an email and pressing ctrl+p is too much effort.

 

[Sir Humphrey Appleby]

^

Found out that I have a major problem with this, I'm finding the persons expected speed at that task but the tasks are broken down into two (or more categories) which I don't really care about as far as this spreadsheet is concerned, basically I have

Name Task Small Large
Fred Bloggs Hardback books 0.2 0.3
Fred Bloggs Paperback books 0.1 0.4

Is there a way to merge the two rows into the same one so the above becomes

Fred Bloggs Books 0.3 0.7

without going through it all myself?

 

[jgold47]

ok need help. this should be a relatively easy question for someone but we cannot seem to figure this out here:


two numbers. Can be positive or negative. Need to calculate the % change between them.

so easy right:

(A1-B1)/B1
a= today b= yesterday
BUT!

A B
100 200
-100 -200
-200 -100
100 -100
-100 -50

its the last few we are having a hard time with.

1= -50%
2=-50% (really its +50%)
3= +100% (should be -100%)
4=+200%
5=100% should be (-100%)

how do I write this formula so it works correctly and changes the sign appropriately?

Abs the numbers doesn't seem to work when doing the negative decreases on negative number

 

[dsgNYC]



ok need help. this should be a relatively easy question for someone but we cannot seem to figure this out here:
two numbers. Can be positive or negative. Need to calculate the % change between them.
so easy right:
(A1-B1)/B1
a= today b= yesterday
BUT!
A B
100 200
-100 -200
-200 -100
100 -100
-100 -50
its the last few we are having a hard time with.
1= -50%
2=-50% (really its +50%)
3= +100% (should be -100%)
4=+200%
5=100% should be (-100%)
how do I write this formula so it works correctly and changes the sign appropriately?
Abs the numbers doesn't seem to work when doing the negative decreases on negative number

Try this formula: =((A1-B1)/B1)*SIGN(B1)

It just changes the sign of your to the sign of "yesterday's" number.

 

[Sir Humphrey Appleby]

Is there a formula to take a date in a cell? I have a column of (lots of) deliveries but they're in the format of "Delivered - 31/08/2012" or "31/08/2012 - delivery to home address" etc. I want to make a column which would have cells with dates in.

 

[suited]



Is there a formula to take a date in a cell? I have a column of (lots of) deliveries but they're in the format of "Delivered - 31/08/2012" or "31/08/2012 - delivery to home address" etc. I want to make a column which would have cells with dates in.

Assumes data is going down Column A, starting with Row 2:

In Cell B2:

=LEFT(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))-1)

In Cell C2:

=RIGHT(A2,LEN(A2)-MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))+1)

Which formula to use (and whether it goes in B2 or C2) will depend on whether the date is to the left or right of the letters.

 

[jgold47]

Thanks for the help on the last question.

I have another one.

I have a look up sheet.

I type in a reference number and it returns information on that reference (poor mans database look up).

I want it to insert a photo of the referenced item.

The photos are stored on an open FTP link, and I have a hyperlink for each photo.

so, what I want to know is, how do you embed a photo, with the location of the photo referenced from a lookup table?

Possible or Programming?

if not possible, I will just have it return the links and people can just click...