Styleforum › Forums › General › General Chat › The Excel Questions Thread
New Posts  All Forums:Forum Nav:

The Excel Questions Thread - Page 34

post #496 of 528
7/17/12 at 3:35am
Quote:
Originally Posted by stevent View Post

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.
Reply
Reply
post #497 of 528
7/17/12 at 4:08am
Click the arrow to expand "Number" under the Home ribbon. Under the Number tab look for "Special" and then Phone Number.
Reply
Reply
post #498 of 528
7/17/12 at 4:13am
Quote:
Originally Posted by rajesh06 View Post

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.
Reply
Reply
post #499 of 528
7/17/12 at 8:53pm
Quote:
Originally Posted by vaulter1 View Post

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 nod[1].gif

That's exactly what I was looking for, thanks so much!
Reply
Reply
post #500 of 528
7/26/12 at 4:40am
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.
Reply
Reply
post #501 of 528
7/26/12 at 2:56pm
You should be able to handle something with this level of complexity with a pivot table
Reply
Reply
post #502 of 528
7/26/12 at 4:20pm
Quote:
Originally Posted by rajesh06 View Post

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?
Reply
Reply
post #503 of 528
7/29/12 at 10:59am
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.)
Reply
Reply
post #504 of 528
7/31/12 at 6:01pm
Quote:
Originally Posted by rajesh06 View Post

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.
Reply
Reply
post #505 of 528
8/2/12 at 2:04am
^

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?
Reply
Reply
post #506 of 528
8/30/12 at 8:08am
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
Reply
Reply
post #507 of 528
8/30/12 at 9:19pm
Quote:
Originally Posted by jgold47 View Post

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.
Reply
Reply
post #508 of 528
8/31/12 at 11:34am
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.
Reply
Reply
post #509 of 528
8/31/12 at 11:55am
Quote:
Originally Posted by Sir Humphrey Appleby View Post

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.
Reply
Reply
post #510 of 528
9/6/12 at 5:57am
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...
Reply
Reply
New Posts  All Forums:Forum Nav:
  Return Home
  Back to Forum: General Chat
Styleforum › Forums › General › General Chat › The Excel Questions Thread