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

The Excel Questions Thread - Page 33

post #481 of 527
1/3/12 at 2:22pm
Quote:
Originally Posted by cahlersmeyer View Post

haha yes, I'm a journalism major... I took MATHS 108 and thats it lol

I guess you skipped over high school algebra.

facepalm.gif
Reply
Reply

Featured Stories on Styleforum

Follow Styleforum on Facebook for the latest news and our random ramblings
Fedora Lounge on Aero Leather jackets
The official thrift/discount store bragging thread
post #482 of 527
1/7/12 at 2:27pm
Excel '03

How to make it stop autoformatting things as dates? And don't say "format the cell before you enter a value".
Reply
Reply
post #483 of 527
1/7/12 at 2:41pm
Quote:
Originally Posted by cahlersmeyer View Post

Just found this thread, so glad it exists.
I'm looking into comparing the cost of buying a Soda Stream machine vs buying soda from the store. I'm trying to figure out how many bottles I have to make in order for me to make the 79.99 machine and flavoring supplies worth it vs buying soda from the store. I've seen this excel sheet before, and did it my freshman year in college in my ISOM class, but don't know what its called? Any ideas?

Someone did this on a review of it. You really dont save much, or anything at all. The conclusion was, savings is no reason to buy it.
Reply
Reply
post #484 of 527
1/17/12 at 12:18pm
I have an excel sheet of 25,000 rows where one column consists of 4-6 digit numbers, which sometimes are followed by a letter(A,B,C,D).

Is there any way to automatically remove these letters and place them into an adjoining column?

Existing condition: Column A = 956732C

Desired result: Column A = 956732 & Column B = C

I'm thinking some sort of if statement for column b and then a find and replace to get rid of the letters in column a?

Halp!
Reply
Reply
post #485 of 527
1/17/12 at 12:56pm
Quote:
Originally Posted by Reborn View Post

I have an excel sheet of 25,000 rows where one column consists of 4-6 digit numbers, which sometimes are followed by a letter(A,B,C,D).
Is there any way to automatically remove these letters and place them into an adjoining column?
Existing condition: Column A = 956732C
Desired result: Column A = 956732 & Column B = C
I'm thinking some sort of if statement for column b and then a find and replace to get rid of the letters in column a?
Halp!

Look into the "Delimiter" function.
Reply
Reply
post #486 of 527
1/17/12 at 1:31pm
Quote:
Originally Posted by deadly7 View Post

Excel '03
How to make it stop autoformatting things as dates? And don't say "format the cell before you enter a value".

is prefixing the cell with ' not what you are looking for?

'January 2005

will be displayed as January 2005 but not turned into 1/1/2005 internally.
Reply
Reply
post #487 of 527
1/18/12 at 8:05am
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?
Reply
Reply
post #488 of 527
1/18/12 at 8:48am
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?

sumifs (or sumif as an array if you don't have office >=2007)

Actually, if you just want to count the days (as opposed to add up some other column based on the days), countif is probably what you are looking for...just be careful with its behavior on non-number values.
Reply
Reply
post #489 of 527
1/18/12 at 2:55pm
Quote:
Originally Posted by otc View Post

sumifs (or sumif as an array if you don't have office >=2007)
Actually, if you just want to count the days (as opposed to add up some other column based on the days), countif is probably what you are looking for...just be careful with its behavior on non-number values.


Yeah that could work. Ended up just doing pivot tables
Reply
Reply
post #490 of 527
4/5/12 at 4:56pm
I need some help with an if then statement. I'm trying to have a cell auto convert currency depending on the currency label:

=if((AND($F6=1,$AJ6="KRW"),$AI6*Settings!$D$7($F6=1,$AJ6>="JPY"),$AI6*Settings!$D$8))

This yields an error.

This works:
=if(AND($F6=1,$AJ6="KRW"),$AI6*Settings!$D$7)

but I need to have it be able to pull in different conversion rates from the Settings worksheet depending on the currency label in AJ6.

Thanks!
Reply
Reply
post #491 of 527
4/5/12 at 8:08pm
you gotta tell us more about the error.

If you can go into the debug mode, where in the evaluation does it hit a snag? really though if that is copy pasted exactly, you've got an extra parentheses in front of the and and you seem to be missing a comma after Settings!$D$7

Really though, I think your life would be easier if you created a hidden column with the exchange rate and set that equal to a lookup (or index-match) to the currency conversion table on your settings page....that way it would work with any number of currencies where as now you would need more and more nested if statements.
Reply
Reply
post #492 of 527
7/12/12 at 7:52am
suppose you have the data in cells A1 to A9:

A1 A
A2 B
A3 C
A4 D
A5 E
A6 F
A7 G
A8 H
A9 I

how do you let the data display such that they get picked in fixed intervals?
e.g. In a two row jump, it displays in

A1 A
A2 D
A3 G

Really appreciate any advice you have!
Reply
Reply
post #493 of 527
7/12/12 at 8:49am
You should be able to do this with a filter and a "helper" column. The helper column would be the basis of the filter and might be something like 1, 0, 0 , 1, 0, 0 , 1, 0, 0 (Really anything that would have a unique identifier for the rows that you wanted to display.)
Reply
Reply
post #494 of 527
7/16/12 at 9:29pm
I'm having problems with phone numbers.

I have data from CRM 1 that lists numbers as (714) 555-5555 and data from CRM 2 that lists numbers as 7145555555. I need to correlate the two for v/h lookup and what not.

I can format a cell with 7145555555 so that is appears as (714) 555-5555, but the raw data in that cell will always be the original 7145555555.

How I can turn 7145555555 into (714) 555-5555, or vice versa, without formatting? This has to be a common problem. Everyone deals with phone numbers.

EDIT: Also, I use Excel 2010. Is there any way to change the right click>paste options to a list like in Excel 2007, and not these dumb icons?
Reply
Reply
post #495 of 527
7/17/12 at 1:49am
Quote:
Originally Posted by Reggs View Post

I'm having problems with phone numbers.
I have data from CRM 1 that lists numbers as (714) 555-5555 and data from CRM 2 that lists numbers as 7145555555. I need to correlate the two for v/h lookup and what not.
I can format a cell with 7145555555 so that is appears as (714) 555-5555, but the raw data in that cell will always be the original 7145555555.
How I can turn 7145555555 into (714) 555-5555, or vice versa, without formatting? This has to be a common problem. Everyone deals with phone numbers.
EDIT: Also, I use Excel 2010. Is there any way to change the right click>paste options to a list like in Excel 2007, and not these dumb icons?
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
Reply
Reply
New Posts  All Forums:Forum Nav:
  Return Home
  Back to Forum: General Chat

Featured Stories on Styleforum

Follow Styleforum on Facebook for the latest news and our random ramblings
Fedora Lounge on Aero Leather jackets
The official thrift/discount store bragging thread
Styleforum › Forums › General › General Chat › The Excel Questions Thread