MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Formula formatting needed --Please help

Posted by scott on October 11, 2001 1:15 PM

Hello group!
I don't understand excel very well and need help! I want to put my customers names, addresses, state, zip code, e-mail address, product, account receivable, sales tax, accrecbletaxed, amount paid ectera into excel. I have figured out how to make the columns using excel. What I am trying to do is when someone orders in my state, I have to charge them sales tax. I need a formula that when I put the state abreviation, like CA into the state column, it automatically multiplies the accounts receivable amount by the .0575 and then adds that amount into the next column(accrecbletaxed).
I hope I have made myself clear.
Any help would be appreciated--even a name of a good book that would help me understand what I am actually doing:).
Thank you,

Posted by Aladin Akyurek on October 11, 2001 1:26 PM

Supposing that State is in column C and account receivable in G, in column for accrecbletaxed enter:




Posted by Scott on October 11, 2001 2:37 PM

Thanks Aladin,
That worked for one row. Now my problem is when I add additonal rows with "CA" as the state it does not work automatically.
It is probably related to me not knowing something:(.


Posted by IML on October 11, 2001 2:53 PM

I don't mean to step on Aladin's toes, but I think his intent was to have you copy that formula as far down as you think you will have data (ie to row 1000 or something). When CA is not in cell (which includes blank cells) the cell will appear blank.

Posted by scott on October 11, 2001 4:00 PM

Thanks Iml,
Ok! I will have to copy the formula all the way down for about 1000 rows for that column. Is there any easy way to do this copy formula to a column?
I will try and find how to do it but an explanation in laymans terms woud be appreciated:)

Thank you for the help!

Posted by IML on October 12, 2001 6:28 AM

Thanks Iml,

Have a look at the help file under cells, copying for several ways to do this.
What I'd do is click on the cell with your formula in it. You'll see a little square in the lower right hand corner. Cover this with the arrow until a plus appears. Left click and scroll down to row 1000 and release.

Posted by Eric on October 12, 2001 8:28 AM

"copying great distances" or "standing on the toes of giants"

My apologies to IML and Aladin for any toe-stepping here.

Say you have data in a2:a2000 and you want to copy down a formula in b2 all the way to b2000.
copy b2, move over to col a, hit and release the "end" key, then hit "down arrow" one time to get to the bottom of col a, then "right arrow" over to col b, hold down the "shift" key, press and release the "end" key, and hit the "up arrow" key (while "shift" key is still depressed) one time to select b2:b2000, and paste. With some practice you can get pretty fast with this.

Another way is to copy the formula in b2, then Edit|Go to and enter the range b3:b2000, hit "enter" and the range is selected, just hit paste.

IMHO the first way is faster.


Posted by Juan Pablo on October 12, 2001 1:36 PM

What about double clicking on the autofill control (Little box on cell) ? FASTER (NT)

Say you have data in a2:a2000 and you want to copy down a formula in b2 all the way to b2000.

Posted by Fred on October 12, 2001 1:54 PM

Hi Iml and others after,
Thank you for the information. Seems like it works great. Unfortunately one of the columns had a #value problem in it so I spent 3 hours reading, trying:). Got that problem figured out. I feel so over my head trying to learn this program.

Thank you,

Posted by Eric on October 12, 2001 2:43 PM

? I'm not figuring that one out, could you give more detail?

Or tell me where to find it in the help file?

Posted by IML on October 12, 2001 2:48 PM

Hi Iml and others after,

that error is probably the result of you having text (as opposed to a number in column G. You could just delete the formula from that line.
As far as copying the formula goes, the other responses show that there are many ways to accomplish this. I just suggested the way I did because most the auto copying type methods will only copy to the end of your data set. If you are setting up this as template to go beyond that, you may not get the desired result. That's the great thing about this venue, many ways to skin a cat.

Posted by IML on October 12, 2001 2:53 PM

Re: ? I'm not figuring that one out, could you give more detail?

Put =row() in A1 and copy it down a few rows.
In B1 but =A1+1
Now double click on the "handle" and it will copy down as far as you have data in row A.
I figure I started in this string by stepping on Aladin's toes, so I may as well just offend everyone.

sorry juan

Posted by Eric on October 12, 2001 5:01 PM

OMG! Thank you Juan, thank you IML, that's a terrific one!

You've done me quite a service- I'll buy everyone in the string a pair of steel toed boots for the occasion!
Just send me your address and shoe size!