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

# The Excel Questions Thread - Page 33

Quote:
Originally Posted by cahlersmeyer

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

I guess you skipped over high school algebra.

Excel '03

How to make it stop autoformatting things as dates? And don't say "format the cell before you enter a value".
Quote:
Originally Posted by cahlersmeyer

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.
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!
Quote:
Originally Posted by Reborn

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.
Quote:

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.
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?
Quote:
Originally Posted by stevent

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.
Quote:
Originally Posted by otc

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
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!
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.
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!
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.)
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?
Quote:
Originally Posted by Reggs

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
New Posts  All Forums:Forum Nav:
Return Home
Back to Forum: General Chat