2 easy questions for the gurus (formula&character limit related)

[hemjeseti]

hey there great guru's of Excel - i really need your help. I'll get right to it.

FIRST PROB: i have a price column, and i need to reduce the entire column by 15% - I'm fine with taking the price column and pasting it into a new document, reducing, and pasting back into original document. i just can't find info on how to adjust a single column once - everyone wants to create a new column to show the "new price" etc. And i'm not a big excel person so perhaps i'm over complicating this.

SECOND: I have a field called Full Description - it holds all the relative data about a product. I uploaded the date and noticed that the data cuts off. After investigating i learned, it's a text field, and it needs to be a memo field to accept more that 255 characters.

how does one do this?

I look forward to hearing from you guys and thank you in advance for all your help, guidance and wisdom. you guys are awesome!

 

[BenMiller]

Well ... anyone who really uses Excel doesn't really use that button. After you try a formula - for example - AVERAGE, select that cell (just click it once, not a double-click), and look at the bar on top, to the right of Mr. "fx".
You'll see =AVERAGE(5,2,9,4) or whichever numbers you put in.

Once you get to know formulas, you'll learn how to type them manually, without using the fx button. The fx guy gives you a dialog box with fields to input all the information, with a little explanation provided for each one.

Manually doing it would be to type the formula ( =AVERAGE(5,2,9,4) ) out yourself.

Every formula has a different syntax, or setup. Meaning, the variables are set up differently for each formula.

You can also combine formulas. For example, =AVERAGE((A1+A2),75) will give you the average two numbers - the result of A1 plus A2, and the number 75.

The possibilities, as they say, are endless and exciting!

 

[hemjeseti]

Hey Ben - thanks so much for the formula education. I'm def gonna mess with that some more.

I'm still hoping to get some insight on the second question about formatting a cell to hold the 32K allowed (understanding it will only show 1024 characters in the excel doc) b/c i am still getting descriptions cut off once i upload to the database.

 

[BenMiller]

I'm not really sure about that, maybe google around... sorry!

 

[hemjeseti]

that's alright B - you've been a huge help! I'm googlin'. for sure!

 

[hemjeseti]

hey everyone - just an update for ya.

I answered my second question. what i did to convert the db field "Full Description" to a MEMO field was reorg the spread sheet where the order was defined by the character length.

As you recall my issue was descriptions were getting cut off. So i created a new column next to the Full Descrip and in the top cell i did:

=LEN(D1)

= to define a formula
LEN - to specify LENGTH
and
(D1) to designate the cell i'm asking to character count

Then i highlighted the cell - and in the bottom right hand corner of said cell i grabbed the dot - and drug it down all the way to the bottom of my list.

Then i clicked to highlight the cell
Went to Data>Sort>

Window pops up, and i select the option that signifies the column that holds the character length - in my case it was called, 11

I selected Descending so that my longest Full Description was first - and saved it.

Then when i did my upload it grabbed the data from the first line in the spread sheet and used that as parameters to define how the field in the db was designed. GENERAL - MEMO FIELD - 32K Character Limit - in the spread sheet it will only show 1024 characters just fyi

ALSO - be SURE to remove your new column you created - your spread sheet should maintain the order you've set but for me, the additional field caused conflict in my upload b/c the db didn't have that field. not to say that it might not be a bad idea to have that field in your db - but mine doesn't.

hope this helps a lil for the next person who comes along - and thanks again to Ben for all his formula help! He made it seem easy.

Take care and make everything ya touch, great.

 

[BenMiller]

Glad to hear it worked out!

 

[hemjeseti]

you and meeeeee both - you can see the site - it's so not done yet - but it's at gopainlesswiring.com

thanks again for your awesomeness!

 

[BenMiller]

Checked it out. You still have the instructions for setting up the website sitting on the homepage btw ...

 

[hemjeseti]

yeah i know - still workin' on it - thought it was more important to get the data in there. and spruce it up later! i'll be updating it this week so, it'll be good to go come jan 1