|
|
|
|
|
|
Welcome to the Australian Ford Forums forum. You are currently viewing our boards as a guest which gives you limited access to view most discussions and inserts advertising. By joining our free community you will have access to post topics, communicate privately with other members, respond to polls, upload content and access many other special features without post based advertising banners. Registration is simple and absolutely free so please, join our community today! If you have any problems with the registration process or your account login, please contact us. Please Note: All new registrations go through a manual approval queue to keep spammers out. This is checked twice each day so there will be a delay before your registration is activated. |
|
|||||||
| The Bar For non Automotive Related Chat |
 |
|
|
Thread Tools | Display Modes |
30-05-2011, 02:27 PM
|
#1 | ||
|
not here much anymore
Join Date: Dec 2004
Location: Sthn NSW
Posts: 22,918
|
Hoping someone can help, I need a formula to take a number entered into one cell, multiply it by 9 and then put the total into another cell.
Any ideas how to do it?
__________________
2024 F150 XLT
|
||
|
|
|
30-05-2011, 02:30 PM
|
#2 | ||
|
Adapt or perish...
Join Date: Jan 2005
Location: Dip!@#$
Posts: 7,954
|
Select the cell you want.
Enter in "=Cell*9" without the ", cell being the cell you want the number entered into (ie: A5, B10, etc etc)
__________________
Carless
|
||
|
|
|
|
30-05-2011, 02:35 PM
|
#3 | |||
|
not here much anymore
Join Date: Dec 2004
Location: Sthn NSW
Posts: 22,918
|
Quote:
The number will be entered into C14 then multipled by 9 and placed into C15, so formula should be =C14*9 yeah?
__________________
2024 F150 XLT
|
|||
|
|
|
|
30-05-2011, 02:36 PM
|
#4 | ||
|
Adapt or perish...
Join Date: Jan 2005
Location: Dip!@#$
Posts: 7,954
|
If you don't have a value within C14 then you'll get that error.
The formula needs to be in C15.
__________________
Carless
|
||
|
|
|
|
30-05-2011, 02:43 PM
|
#5 | ||
|
Awesome
Join Date: Oct 2007
Location: In my own little world..Everyone here knows me :)
Posts: 9,401
|
You can put a zero in C14 if you do not have the number but want to set up the formula. Create the formula in C15 =C14*9. It will calculate.
 Having said that, it should bring up a zero in C15 if you just put that formula in anyway without any number in C14, unless C14 is a calculation of other cells. Cheers Col
__________________
Last edited by Geez Louise; 30-05-2011 at 02:48 PM. Reason: Added more ...just like Excel...HeHe |
||
|
|
|
|
30-05-2011, 02:52 PM
|
#6 | ||
|
not here much anymore
Join Date: Dec 2004
Location: Sthn NSW
Posts: 22,918
|
Got that one sorted, thanks all. Will advise when I get to next problem!
__________________
2024 F150 XLT
|
||
|
|
|
|
30-05-2011, 02:57 PM
|
#7 | ||
|
not here much anymore
Join Date: Dec 2004
Location: Sthn NSW
Posts: 22,918
|
Next one:
Need to develop a formula where we have 2 types of chemical being used. One is known as R, the other is NR. If NR is used I want to add 10% to total of job. If the spreadsheet user puts 'NR' into E28, whats the formula to get it to add 10% to the total of the job?
__________________
2024 F150 XLT
|
||
|
|
|
|
30-05-2011, 03:11 PM
|
#8 | ||
|
Waiting
Join Date: Mar 2006
Location: Alice Springs, NT
Posts: 105
|
=IF(E28="NR",C14*9*1.1,C14*9)
Note that formulas are not case sensitive
__________________
1971 XY Fairmont GS Factory 351C & Toploader- Electric Blue & Convo Pros 1984 Bronco XLT 351 C6 4WD - Black/ Silver LPG, 3" Lift, American Racing Wheels 2007 Jeep Wrangler Unlimited CRD Renegade- Black 2009 FG XR6 Vixen |
||
|
|
|
|
30-05-2011, 03:13 PM
|
#9 | ||
|
Awesome
Join Date: Oct 2007
Location: In my own little world..Everyone here knows me :)
Posts: 9,401
|
Where are you putting the total of E28?
if into E29 then I think this is 10% =E28*1.1
__________________
|
||
|
|
|
|
30-05-2011, 03:24 PM
|
#10 | ||
|
not here much anymore
Join Date: Dec 2004
Location: Sthn NSW
Posts: 22,918
|
What it needs to do is add the figures:
E14+E26+E28 (E28 is the 10% one so it needs to know that 'R'=add 0% but 'NR'=add 10%, then add the figure in E30 if there is one in there, and have the total come out at E32. Make sense?
__________________
2024 F150 XLT
|
||
|
|
|
|
30-05-2011, 03:30 PM
|
#11 | ||
|
Adapt or perish...
Join Date: Jan 2005
Location: Dip!@#$
Posts: 7,954
|
=(e14+e26)+if(e28="NR",e32*1.1,0)
Give that one a go and see what happens.
__________________
Carless
|
||
|
|
|
|
30-05-2011, 03:34 PM
|
#12 | ||
|
Awesome
Join Date: Oct 2007
Location: In my own little world..Everyone here knows me :)
Posts: 9,401
|
Yep. That makes sense.
in E32 put this: =E14+E26+E28*1.1 There are other more fancy forumulas but that will do what you want it to do. I get lazy when it comes to Excel...  Rapid Axe obviously works with it a lot! 
__________________
|
||
|
|
|
|
30-05-2011, 03:40 PM
|
#13 | ||
|
Adapt or perish...
Join Date: Jan 2005
Location: Dip!@#$
Posts: 7,954
|
Yes, yes I do.
Learned alot through an old job I was in (to the point I programmed in the background using VB to update price files we had in place)
__________________
Carless
|
||
|
|
|
|
30-05-2011, 03:42 PM
|
#14 | ||
|
Adapt or perish...
Join Date: Jan 2005
Location: Dip!@#$
Posts: 7,954
|
Misread a post.
In my top formula, add +E30 to the end of the bracketed section. It'll add the cell regardless of if a value is present or not.
__________________
Carless
|
||
|
|
|
|
30-05-2011, 03:45 PM
|
#15 | ||
|
Awesome
Join Date: Oct 2007
Location: In my own little world..Everyone here knows me :)
Posts: 9,401
|
Yes...the joys of Excel. I remember back to the old Lotus 123 days. None of this fancy little paperclip wizard to help you!
  One thing I hate about Excel...PIVOT TABLES!! It has taken me ages to master them and even then, I think most of the time I fluke it on shear luck
__________________
|
||
|
|
|
|
30-05-2011, 03:50 PM
|
#16 | ||
|
FF.Com.Au Hardcore
Join Date: Nov 2009
Location: Ipswich, Qld
Posts: 1,354
|
Haha, Pivot tables are awesome! If there's a day that I want to really 'do my head in' - that's where I start!
I must admit though, I'd rather VB in Access than VB in Excel...
__________________
----------------------------------------------------- 2012 Focus ST Tangerine Scream Continually having a battle of wits with unarmed opponents. Sez  Photo's by Sez |
||
|
|
|
|
30-05-2011, 07:16 PM
|
#17 | ||
|
Back where I belong
Join Date: Jan 2005
Location: Mexico - Victoria
Posts: 947
|
if you keep adding to E30 soon enough you will have E85 and in need for a new tune... :P
__________________
Regards Craig |
||
|
|
|
|
30-05-2011, 08:18 PM
|
#18 | ||
|
FF.Com.Au Hardcore
Join Date: Jun 2005
Posts: 5,193
|
OH how I love a PIVOT TABLE. Always funny waiting for someone to calculate something for hours even days on end then doing a PIVOT TABLE infront of them in 5 seconds to get the same result.
They will never forget how to do one after that.
|
||
|
|
|
|
30-05-2011, 08:31 PM
|
#19 | |||
|
FF.Com.Au Hardcore
Join Date: Apr 2007
Location: Miranda, NSW
Posts: 6,771
|
Quote:
Yep I've used spreadsheets for around 24 years , being an accountant has a bit to do with it I guess .....First Lotus 123 then Excel from mid 90's onward. Also hate pivot tables, probably because I haven't had the time to sit down and learn them and now have a team who do all those mechanical tasks for me, so now don't really want to know. Best feature in modern spreadsheeting in Excel v the old Lotus is the Undo function (helped me out heaps) ands Goal Seek
__________________
2005 BA MK2 FPV GT - 6 SPEED MANUAL , SILHOUETTE, SWISSVAX, SUNROOF, BILSTEIN AND LOVELLS, FACTORY GENUINE 19'S, X-FORCE STAINLESS QUAD CATBACK, ADVANCE HEADERS, 200 CPSI CATS, BLUEPOWER CAI, HERROD BREATHER KIT, 4:11 DIFF RATIO, MAL WOOD OPT 3+ CLUTCH, BILLET SHIFTER, MELLINGS 10227, NOW WITH REVERSE CAMERA/SENSORS, ALPINE SPEAKERS & SUB - CUSTOM TUNED TO 275 RWKW NOW WITH A NEW ADDITION - 2017 MUSTANG V8 GT FASTBACK - , 6 SPEED AUTO IN PLATINUM WHITE, Last edited by GT0132; 30-05-2011 at 08:38 PM. |
|||
|
|
|
|
30-05-2011, 08:49 PM
|
#20 | ||
|
Awesome
Join Date: Oct 2007
Location: In my own little world..Everyone here knows me :)
Posts: 9,401
|
The amount of times I get a circular reference error because I am being lazy.
Don't get me wrong, Excel is great and I have written more than a few speadsheets with macros etc in my time...but I just don't have the patience for long winded formulas anymore.
__________________
|
||
|
|
|
|
30-05-2011, 10:47 PM
|
#21 | ||
|
FF.Com.Au Hardcore
Join Date: Aug 2006
Location: Salamander Bay
Posts: 5,427
|
looks like you are doing a job quoting system if this is the case you would be better off using access and running a queries to do your calculations this will give you more flexibility and you will have a permanent record of all jobs quoted
__________________
[SIGPIC][/SIGPIC]
Everyone starts off with a bag full of luck and an empty bag of experience. The trick is to fill the experience bag before the luck bag is empty. "It is better to remain silent and be thought a fool than to speak out and remove all doubt." Start a new career as a bus driver Rides: FG2 XR6 stock at this stage but a very nice ride xc 4 DOOR X CHASER 5.8 UNDER RESTO |
||
|
|
|
|
30-05-2011, 11:18 PM
|
#22 | ||
|
FF.Com.Au Hardcore
Join Date: Mar 2008
Location: Melbourne
Posts: 10,839
|
mmm the joys of excel - vlookups, whatif statements, pivot tables....
Access for the win! |
||
|
|
|
|
30-05-2011, 11:39 PM
|
#23 | ||
|
FF.Com.Au Hardcore
Join Date: Apr 2005
Location: Canberra
Posts: 13,448
|
Probably about the most simple article of the Excel vs Access argument you're going to get.
http://office.microsoft.com/en-us/ac...001042918.aspx |
||
|
|
|
|
31-05-2011, 12:11 AM
|
#24 | |||
|
CLEVO POWERED
Join Date: Mar 2009
Location: QLD
Posts: 1,625
|
Quote:
Sorry to be OT 
|
|||
|
|
|
|
31-05-2011, 10:01 AM
|
#25 | ||
|
not here much anymore
Join Date: Dec 2004
Location: Sthn NSW
Posts: 22,918
|
Morning all,
latest one for you! Need to create a formula where a value entered into a cell will be multiplied by 100 e.g. '3' is placed into E19 and will then turn into '300' (3x100=300). Any ideas on the forumla? This is the last bit I need.
__________________
2024 F150 XLT
|
||
|
|
|
|
31-05-2011, 10:12 AM
|
#26 | ||
|
Adapt or perish...
Join Date: Jan 2005
Location: Dip!@#$
Posts: 7,954
|
You mean this way???
Type "3" into cell E19 Turns value into 300 automatically? Don't think it can work that way. You'll need another cell to use as a reference.
__________________
Carless
|
||
|
|
|
|
31-05-2011, 10:20 AM
|
#27 | |||
|
Cane Farmer
Join Date: Jun 2006
Location: Tom Price, WA
Posts: 4,056
|
You can do it by adding custom formatting the cell to show 300...although this is cosmetic, the number in the cell is still 3.
if you want to do it that way, custom format 0"00", apply to the cells you want to multiply... Other than that, you'd have to stuff around in VB...or as Rapid Axe suggested, you need a cell as a reference. Hope this helps..
__________________
1994 ED XR6T - Cobalt Blue. 2009 FG XR6 - Black. Quote:
|
|||
|
|
|
|
31-05-2011, 11:04 AM
|
#28 | ||
|
Adapt or perish...
Join Date: Jan 2005
Location: Dip!@#$
Posts: 7,954
|
What I used to do in that particular case was to have another cell as a reference for the formula to work but then made the text white to blend in and then locked the cell so it couldn't be edited.
Still keeps the page looking clean.
__________________
Carless
|
||
|
|
|
|
31-05-2011, 11:21 AM
|
#29 | |||
|
Cane Farmer
Join Date: Jun 2006
Location: Tom Price, WA
Posts: 4,056
|
I'm sensing a fellow excel guru. Haha.
I have a cup on my desk that has a big red heart followed by 'Excel' hahaha
__________________
1994 ED XR6T - Cobalt Blue. 2009 FG XR6 - Black. Quote:
|
|||
|
|
|
|
31-05-2011, 01:00 PM
|
#30 | |||
|
not here much anymore
Join Date: Dec 2004
Location: Sthn NSW
Posts: 22,918
|
Quote:
I've re-hashed the template to get around the cell multiplication issue.
__________________
2024 F150 XLT
|
|||
|
|
|
Excel formula help?
Linear Mode
