| Author | |
|---|
Apedemak
Villager
| Joined: | Sunday October 17th, 2004 |
| Location: | |
| Posts: | 3135 |
| Photo: | [Download] |
| Status: |
Offline
|
| Mana: |   |
Click here for your Black Profile
Search for Black Sites
|
Posted: Friday November 10th, 2006 13:08 |
|
Ok I'm back doing battle with excel again a bit rusty after my last run in with it but things are really moving now and I need to sort this out.
- How do you add to the value of something? I need to add VAT (17.5%) to a formula but it won't add to its value properly... can anyone help?
- How do I copy formlas? Too many lines to be typing it all in again. Has to change to suit the new line.
- On some lines I'm getting a monetary value rather than just a numerical one, showing -£1.00- rather than just -1- how do I change it?
I'm working on an inventory list and I've finally got some stock in, really wanna get it down tight and intergrate the inventory with my sales sheet and cashflow so at the end of the day/week someone can total up the days sales and have it all work itself out, have also managed to intergrate it with a cool looking line chart showing how many of each product has been sold.
If anyone can help or point me in the right direction it'd be brilliant, I don't like that paperclip thing it just annoys me.
Any advice is appreciated.
Peace 
RL
Last edited on Friday November 10th, 2006 13:57 by Apedemak
____________________
____________________
Click here for your Black Profile
|
Vezz.
Villager
Click here for your Black Profile
Search for Black Sites
|
Posted: Friday November 10th, 2006 15:01 |
|
Rebel-Lion wrote:
Ok I'm back doing battle with excel again a bit rusty after my last run in with it but things are really moving now and I need to sort this out.
- How do you add to the value of something? I need to add VAT (17.5%) to a formula but it won't add to its value properly... can anyone help?
i.e 100*17.5%+100=117.50
- How do I copy formlas? Too many lines to be typing it all in again. Has to change to suit the new line.
hover over the bottom right of the cell, click then drag
- On some lines I'm getting a monetary value rather than just a numerical one, showing -£1.00- rather than just -1- how do I change it?
Click on the cell, or highlight the columns or rows, right click then choose format cells, then choose number and ok.
I'm working on an inventory list and I've finally got some stock in, really wanna get it down tight and intergrate the inventory with my sales sheet and cashflow so at the end of the day/week someone can total up the days sales and have it all work itself out, have also managed to intergrate it with a cool looking line chart showing how many of each product has been sold.
If anyone can help or point me in the right direction it'd be brilliant, I don't like that paperclip thing it just annoys me.
Any advice is appreciated.
Peace
RL
Any more help, just holla!
Last edited on Friday November 10th, 2006 15:03 by Vezz.
____________________
www.blacksearch.co.uk - Helping to promote Black African and Caribbean Websites
|
Apedemak
Villager
| Joined: | Sunday October 17th, 2004 |
| Location: | |
| Posts: | 3135 |
| Photo: | [Download] |
| Status: |
Offline
|
| Mana: | |
Click here for your Black Profile
Search for Black Sites
|
Posted: Friday November 10th, 2006 16:34 |
|
Whooo!
Excel-lent!
The old man is pissed off as well, always a good sign. 
Got all that, the VAT and everything but now I'm stuck on how to get all formulas in one. So far I'm doing this;
=G5+G6+G7+G8...
And so on and so forth but if I need to add more inventory it probably won't show and by the time I'm finished I'll look like Lloyd but with a hunched back and curled fingers.
No one deserves that. .lol.
Might stay up all night and finish this.
Thanks again.
Last edited on Friday November 10th, 2006 16:36 by Apedemak
____________________
____________________
Click here for your Black Profile
|
Vezz.
Villager
Click here for your Black Profile
Search for Black Sites
|
Posted: Friday November 10th, 2006 17:11 |
|
I actually answered this as I thought you asked for it!!!
Enter the formula in a cell, then drag it into the cells you want to populate.
Any more?
____________________
www.blacksearch.co.uk - Helping to promote Black African and Caribbean Websites
|
Vezz.
Villager
Click here for your Black Profile
Search for Black Sites
|
Posted: Friday November 10th, 2006 17:15 |
|
Rebel-Lion wrote: Whooo!
Excel-lent!
The old man is pissed off as well, always a good sign.
Got all that, the VAT and everything but now I'm stuck on how to get all formulas in one. So far I'm doing this;
=G5+G6+G7+G8...
And so on and so forth but if I need to add more inventory it probably won't show and by the time I'm finished I'll look like Lloyd but with a hunched back and curled fingers.
No one deserves that. .lol.
Might stay up all night and finish this.
Thanks again.
Misread again...need to get some sleep!
=sum(cell references). It will highlight the cells, so you just drag your cursor.
I hope that makes sense!
____________________
Click here for your Black Profile
|
TheDogon
Villager
| Joined: | Thursday May 11th, 2006 |
| Location: | USA |
| Posts: | 1605 |
| Photo: | |
| Status: |
Offline
|
| Mana: |  |
Click here for your Black Profile
Search for Black Sites
|
Posted: Friday November 10th, 2006 17:25 |
|
Vezz. wrote: Rebel-Lion wrote: Whooo!
Excel-lent!
The old man is pissed off as well, always a good sign.
Got all that, the VAT and everything but now I'm stuck on how to get all formulas in one. So far I'm doing this;
=G5+G6+G7+G8...
And so on and so forth but if I need to add more inventory it probably won't show and by the time I'm finished I'll look like Lloyd but with a hunched back and curled fingers.
No one deserves that. .lol.
Might stay up all night and finish this.
Thanks again.
Misread again...need to get some sleep!
=sum(cell references). It will highlight the cells, so you just drag your cursor.
I hope that makes sense!
Shoot, he could even just go to a cell and type "+(" then drag and highlight all the cells in the column he want to add up. Then close it with a ")". That will add all of the cells and put the value in the starting cell.
____________________
“If there is no struggle, there is no progress. Those who profess to favor freedom, and deprecate agitation, are men who want crops without plowing up the ground, they want rain without thunder and lightning.
http://www.covenantwithblackamerica.com
____________________
www.blacksearch.co.uk - Helping to promote Black African and Caribbean Websites
|
Vezz.
Villager
Click here for your Black Profile
Search for Black Sites
|
Posted: Friday November 10th, 2006 17:28 |
|
I didn't mean he should enter the references one by one!
It's the same thing, different method.
I think it was Excel 2000 that didn't allow you to do it the way you described, so I've always done it the =sum way.
____________________
Click here for your Black Profile
|
Vezz.
Villager
Click here for your Black Profile
Search for Black Sites
|
Posted: Friday November 10th, 2006 17:29 |
|
Last edited on Friday November 10th, 2006 17:30 by Vezz.
____________________
www.blacksearch.co.uk - Helping to promote Black African and Caribbean Websites
|
Vezz.
Villager
Click here for your Black Profile
Search for Black Sites
|
Posted: Friday November 10th, 2006 17:30 |
|
Last edited on Friday November 10th, 2006 17:31 by Vezz.
____________________
Click here for your Black Profile
|
TheDogon
Villager
| Joined: | Thursday May 11th, 2006 |
| Location: | USA |
| Posts: | 1605 |
| Photo: | |
| Status: |
Offline
|
| Mana: | |
Click here for your Black Profile
Search for Black Sites
|
Posted: Friday November 10th, 2006 17:39 |
|
Vezz. wrote: I didn't mean he should enter the references one by one!
It's the same thing, different method.
I think it was Excel 2000 that didn't allow you to do it the way you described, so I've always done it the =sum way.
I understood you. But I am using 2003. They restored that feature, I guess.
____________________
“If there is no struggle, there is no progress. Those who profess to favor freedom, and deprecate agitation, are men who want crops without plowing up the ground, they want rain without thunder and lightning.
http://www.covenantwithblackamerica.com
____________________
www.blacksearch.co.uk - Helping to promote Black African and Caribbean Websites
|
Apedemak
Villager
| Joined: | Sunday October 17th, 2004 |
| Location: | |
| Posts: | 3135 |
| Photo: | [Download] |
| Status: |
Offline
|
| Mana: | |
Click here for your Black Profile
Search for Black Sites
|
Posted: Friday November 10th, 2006 18:10 |
|
Vezz's one seemed to work, not sure what version I'm using.
Its managed to get the total wrong though, its got the total value fine but the total value plus VAT is 5p off. Can't be the formula, unless it rounded it all down or something.
Baffling...
Won't change it with the format function either.
Slight hiccup trying to get all the stock list onto a next page. Can cut and paste but it dosen't change when the inventory is updated.
I'll ask the clip....
Last edited on Friday November 10th, 2006 18:53 by Apedemak
____________________
____________________
Click here for your Black Profile
|
Apedemak
Villager
| Joined: | Sunday October 17th, 2004 |
| Location: | |
| Posts: | 3135 |
| Photo: | [Download] |
| Status: |
Offline
|
| Mana: | |
Click here for your Black Profile
Search for Black Sites
|
Posted: Friday November 10th, 2006 19:57 |
|
Got it.. not sure why it was pissing me off.
Still got the VAT 5p short though.
____________________
____________________
www.blacksearch.co.uk - Helping to promote Black African and Caribbean Websites
|
Vezz.
Villager
Click here for your Black Profile
Search for Black Sites
|
Posted: Friday November 10th, 2006 20:10 |
|
I've never been able to fix the rounding problem in all my years of using Excel.
Do you get a green triangle in the corner of the cell?
____________________
Click here for your Black Profile
|
Apedemak
Villager
| Joined: | Sunday October 17th, 2004 |
| Location: | |
| Posts: | 3135 |
| Photo: | [Download] |
| Status: |
Offline
|
| Mana: | |
Click here for your Black Profile
Search for Black Sites
|
Posted: Friday November 10th, 2006 20:56 |
|
Nope... it should be ok.
Its kinda coming together, feel a bit hungry now though surviving off of melted cola bottles and cigs.
Gonna have to carry on tomorrow.
____________________
____________________
www.blacksearch.co.uk - Helping to promote Black African and Caribbean Websites
|
Vezz.
Villager
Click here for your Black Profile
Search for Black Sites
|
Posted: Friday November 10th, 2006 21:14 |
|
Well, get some rest and see what tomorrow brings.....
Excel is a program you need to use all the time to get the hang of - not like Word.
Excel makes my life a whole lot easier....
____________________
Click here for your Black Profile
|
tantalise
Villager
| Joined: | Wednesday May 5th, 2004 |
| Location: | |
| Posts: | 61 |
| Photo: | |
| Status: |
Offline
|
| Mana: | |
Click here for your Black Profile
Search for Black Sites
|
Posted: Sunday November 12th, 2006 12:14 |
|
To get rid of rounding errors: (excel 2003)
try:
highlight the required area
right click
select currency or accounting
adjust the decimal places thingy accordingly
should do the trick
____________________
www.blacksearch.co.uk - Helping to promote Black African and Caribbean Websites
|
Apedemak
Villager
| Joined: | Sunday October 17th, 2004 |
| Location: | |
| Posts: | 3135 |
| Photo: | [Download] |
| Status: |
Offline
|
| Mana: | |
Click here for your Black Profile
Search for Black Sites
|
Posted: Sunday November 12th, 2006 15:40 |
|
Yeah... it kinda adds a decimal point makes it 400.006 rather than just 400.06 annoying but it should be alright.
Haven't been working on it this weekend, too much to do. We should be opening on the 25th have to be done before then as well as sort the other tid bits at the same time.
The next dilemma is the sales sheet. Bit tough to explain...
On the inventory list I have;
Product code Description Quantity
RM-RCDA500 Sean Paul Trinty 5
Can get the sales sheet to tally the quantity, the price and all that but when entering what has been sold into the sales sheet is there a way it can recognise either the product code or description so it can deduct the right amount from the right product line? So one page can recognise that I'm inputting information thats in another and keep the information of that product... or is it best to just use the formulas from the inventory sheet to do the sales?
Thanks.
rl
Last edited on Sunday November 12th, 2006 16:11 by Apedemak
____________________
____________________
Click here for your Black Profile
|
Vezz.
Villager
Click here for your Black Profile
Search for Black Sites
|
Posted: Sunday November 12th, 2006 19:19 |
|
I think I understand - apologies if wrong answer.
If you click on a cell and enter =, then click on any other cell, in any spreadsheet or page it will pick up that particular cell, i.e in spreadsheet Joe Blogs you have 5 in A1, 6 in A2, 7 in A3, 8 in A4 etc, you open Mrs Blog's spreadsheet, type = in cell B1, go back to Joe Blogs's spreadsheet and click on A1+A2+A3+A4 then enter, it will show the total of 26.
I've tried to =sum it in previous excels and it didn't work so, as a creature of habit I've stuck to the "hard" way.....
____________________
www.blacksearch.co.uk - Helping to promote Black African and Caribbean Websites
|
Apedemak
Villager
| Joined: | Sunday October 17th, 2004 |
| Location: | |
| Posts: | 3135 |
| Photo: | [Download] |
| Status: |
Offline
|
| Mana: | |
Click here for your Black Profile
Search for Black Sites
|
Posted: Monday November 13th, 2006 10:45 |
|
I've got that.
I need to get one sheet to recognise whats in the other without the =Sheet1! forumula.
Heres an example of whats on the inventory sheet;
Product Code Product Description Quantity Cost inc VAT Sells for
RM-RCD0001 Sean Paul 5 £10.75 £12
RM-RCD0002 Busta Rhymes 5 £9.75 £12
RM-RCD0003 Nas - Illmatic 5 £8.50 £10
But on the sales sheet there needs to be a way it can recognise the product that was sold.
Sales sheet;
Day 1
Product Code Product Description Units £Profit
RM-RCD0002 Busta Rhymes 3 2.25
As the units are sold it should count down and take away from the correct line. So now Busta Rhymes would read;
Product Code Product Description Quantity Cost inc VAT Sells for
RM-RCD0001 Sean Paul 5 £10.75 £12
RM-RCD0002 Busta Rhymes 4 £9.75 £12
RM-RCD0003 Nas - Illmatic 5 £8.50 £10
So the sales sheet would recognise that it was a Busta Rhymes CD that was sold, deduct from the quantity on that line and work out the profit made depending on how many units were sold. So far I can't get it to recognise each line for it to deduct from so I'm stuck having to enter each line and formula on its own each time a sale is made. It should be able to recognise the whole line from the product code so who ever is using it can just type in;
Day 1
Product Code Product Description Units £Profit
RM-RCD0002 Busta Rhymes 3 6.75
^ And it would recognise and know what to do with the rest.
----edditt---
Last edited on Monday November 13th, 2006 19:13 by Apedemak
____________________
____________________
Click here for your Black Profile
|
|
|
Current time is 00:41 | |
|
