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!

 

[hemjeseti]

Thanks Ben, i found that - so, once a cell is set to a "formula" when you right click and select format cells, it'll open to whatever the cell is formatted with already highlighted. Correct?

 

[BenMiller]

It should. Unless you select multiple cells with different formatting.

 

[hemjeseti]

great, now, where does one specify or put a formula? also, General, allows you to use more than 255 characters - according to what i'd read, can you confirm this?

 

[BenMiller]

What do you mean "specify or put a formula"?

 

[hemjeseti]

well, (gosh i feel like a moron) if you are told to "put this formula here to add these two columns" and it looks like this:

=AVERAGE(IF((A1:A60>=Low)*(A1:A60<=High),A1:A60))

wheeeeeeeere does that go?

with the % thing in my first question - i put .85 in a cell, copied and paste special, multiply - no formula typed - and i've never dealt with formulas so, forgive me if this is the dumbest question ever.

 

[BenMiller]

You're funny Just put that in a blank cell. Any time it says "A1" in the formula, it is referencing that cell. Change the references to whatever cell your data is in.

 

[BenMiller]

Brief synopsis of formulas: Any time you begin typing in a cell with the = symbol (or the + symbol), you have officially begun to type a "formula", which is Excel language for a math problem. For example, typing "=2+2" in a cell (without the quotes) will return 4.

How about this. If you type "2" (always without the quotes) into cell A1, and "2" into cell B1, then in cell C1 you type "=A1+B1", it will also return 4. Try it. Now go change the number in A1 from a 2 to a 3. Look what happens in C1.

And that, my friend, is the kindergarten explanation of what the power of dynamic data (data, or formulas, which change when you change the input) can do for you.

Dream big!

 

[hemjeseti]

wow - thanks Ben - why is that not listed on any website out there. even the "formulas for beginners" sites - they don't break it down so well.

Thank you! now i'm a kindergarten excel moron instead of just a moron.

 

[BenMiller]

Ok, ready for the next step? That was a basic formula - meaning, it just did a regular math problem, like addition or subtraction.

Besides for the obvious, Excel also has a wide variety (about 300 and counting) of formulas to custom-fit your needs.

Easiest example: AVERAGE.

Go to an empty cell (there should be millions of them on your sheet). Up on top, right above where you see A,B,C, etc. there is a little bar which always displays what is contained in the cell which is currently selected.

Immediately to the left of that, you will see a little symbol which looks like a cursive "fx". That's the formula guy. Click on him.

Search for "average", select it, and click OK. In the dialog box which opens up, it will let you enter as many numbers as you want (up to 255), one in each field.

Click OK. Presto! The average of all those numbers is now shown

Basically, that function added up all those numbers then divided them by the amount of numbers there are (the same way you normally would calculate an average), without you having to do it manually.

Dream bigger!

 

[hemjeseti]

wow - that's enlightening. how come no one has said, "f/x" button before. that helps a LOT.