Anyone know how to use excel?

euser1984

I just need a setup where the gross amount is entered and then tax is deducted from that to make the net.

There needs to be an option to use either 13.5% or 23.5?

It's for someone else and tbh I don't know much about these formulas you can add to cells....

Thanks.

srsly78

Cell A1 = put your gross in here
Cell A2 = Applicable tax rate (13.5 or 23.5)
Cell A3 = Net = formula like this -> A1 - ((A1 / 100) * A2)

Or other equivalent maths. Example: (A1 / 100) * (100 - A2). Whatever makes sense in your head. You can then do fancy stuff like making cell A2 a combobox so the user can only pick 13.5 or 23.5.

The formulas are just standard maths, but instead of "x" and "y" you use cell references like A1, A2 etc. There are loads of tutorials for this online, very easy to use, and extremely useful for any kind of business administration.

A Primal Nut

Follow this procedure to create the option to choose between 13.5% or 23.5%.

http://office.microsoft.com/en-ie/excel-help/create-or-remove-a-drop-down-list-HP010342357.aspx

euser1984

I haven't even got excel on my computer and I haven't access to the internet on this persons machine.....

Could someone just upload something I can carry on a usb key or something?

Thanks a million

endacl

euser1984 wrote: »

I haven't even got excel on my computer and I haven't access to the internet on this persons machine.....

Could someone just upload something I can carry on a usb key or something?

Thanks a million

No problem. Shall we discuss a fee first though...?

:rolleyes:

euser1984

endacl wrote: »

No problem. Shall we discuss a fee first though...?

:rolleyes:

A bottle of Karma :P

I'll try and get over to my friends house at some stage first to try it out so....have no car atm, that's why I asked Was worth a shot....

euser1984

euser1984 wrote: »

A bottle of Karma :P

I'll try and get over to my friends house at some stage first to try it out so....have no car atm, that's why I asked Was worth a shot....

If you are looking for excel and you don't want to buy it there is always the alternative which is libraoffice. It's not quite as good but will do the job for what you want. It's the only free legal way of getting a program like excel, well not only cause there is also openoffice but I mean you wont get the official excel for free legally.

http://www.libreoffice.org/

euser1984

[Deleted User] wrote: »

If you are looking for excel and you don't want to buy it there is always the alternative which is libraoffice. It's not quite as good but will do the job for what you want. It's the only free legal way of getting a program like excel, well not only cause there is also openoffice but I mean you wont get the official excel for free legally.

http://www.libreoffice.org/

Thanks for the replies people, sorry for my rudeness, the solution will b like gold to this person.... I'll go over to the house this morning...

Lundar.

Highly agree with using Libreoffice as free alternative office package, I install it on most PC's I fix. easy to use, but takes a little bit of time to get use to it.

dmc17

Deleted User wrote: »

If you are looking for excel and you don't want to buy it there is always the alternative which is libraoffice. It's not quite as good but will do the job for what you want. It's the only free legal way of getting a program like excel, well not only cause there is also openoffice but I mean you wont get the official excel for free legally.

http://www.libreoffice.org/

Google Sheets is another option

euser1984

Hi guys,
The above info received for calculating a figure within a cell worked great guys - thanks for the help on that.


I have another issue that I want to do and at the moment I am googling it but it looks like it might be a little complex.

I have for the gross 2k (column d, row 5) and I need to calculate it as either 13.5 or 23 percent to get the vat figure...the calculation is automatically done in column H and I for both rates. Then I have to just set the vat column (E) to gross minus correct vat result, be it column H or I.

To avoid confusion I will then need to blank 23% (column I, row 5) for example if the rate is 13.5 (in this case it is) . Is there a way I could add a dropbox where I can select one of the tax rates; which, when selected will autofill the E column and add the net to either H or I depending on chosen.


image share

Thanks.

srsly78

Is that screenshot from excel? Or did you use one of the free alternatives?

In Excel what you are looking for is called a "combo box".

http://office.microsoft.com/en-ie/excel-help/add-a-list-box-or-combo-box-to-a-worksheet-HP010236681.aspx

Once you have the combobox set up, you then want to have a macro that runs when the user picks a value. This macro would then update the other cells as per your needs.

Private Sub myCombo_AfterUpdate()
    If Me.myCombo.Value = "23%" Then
       Cells( x, y) = something - (somethingelse * 0.23)
    else 
       Cells(x, y) = something - (somethingelse * 0.125)
End Sub

euser1984

srsly78 wrote: »

Is that screenshot from excel? Or did you use one of the free alternatives?

In Excel what you are looking for is called a "combo box".

http://office.microsoft.com/en-ie/excel-help/add-a-list-box-or-combo-box-to-a-worksheet-HP010236681.aspx

Once you have the combobox set up, you then want to have a macro that runs when the user picks a value. This macro would then update the other cells as per your needs.

Private Sub myCombo_AfterUpdate()
    If Me.myCombo.Value = "23%" Then
    'do stuff
    else 
    ' do other stuff
End Sub

It is excel, sorry. I'll check this out now....

srsly78

Oops, it's combobox_change event you want, not AfterUpdate.

euser1984

It seems the only way I can get a drop down single row box is via some data validation technique...

The only thing I can find in the developer ribbon is a combo box which takes more than one cell....I need to have the option to select either/or on every row....

Overheal

Data validation is how to do this correctly. It's under data tab on the ribbon. Then you just choose the option for list, and you enter your two VAT options

It would honestly take less time to do than to explain, but fish and fishing.

euser1984

OK guys, I have the dropdown box with the two values in now! I'm thinking I really have to know VB to make it work the calculations the way I want?

May be a job for an offshore freelancer or something? Thanks.

srsly78

Or 2 minutes work and some copy+paste:

Alt+f11 to open vba macro editor, paste in code I gave you earlier. Read some basic tutorials if you get stuck.

http://www.excel-vba.com/vba-prog-1-1-editor.htm

tenifan

srsly78 wrote: »

Or 2 minutes work and some copy+paste:

Alt+f11 to open vba macro editor, paste in code I gave you earlier. Read some basic tutorials if you get stuck.

srsly78, this is clearly not a solution for the op!

Data validation is the only way to go.

srsly78

Well give him some advice about it then.

tenifan

srsly78 wrote: »

Well give him some advice about it then.

I did. I said to use data validation.

Overheal

Vb? No. Lol.offshore freelancer sure I'll take a fiver in my steam wallet

Overheal

For your two results you just need conditional statements. Eg. In the cell where you calculated 23% you can have it be blank if our drop list value is 13.5 by saying if(listvalue =23, do formula, "") in laymens terms if the vat selected is 23% output the value of the calculation in the column, if it's anything but 23 leave the cell blank.

That's the most fundamental way to do it. A cleaner way would be to have one column, that changes entirely based on the vat selected. So you would only see a single 13.5 or 23 column if the vat was either respectively, instead of having empty columns. Eg. Where you have simply typed in big letters "13.5%" instead put a formula in this cell, '=(value from drop down list)' and for the formula beneath that, that can accept any value from the dropdpwn list. That way it's designed such that if vat ever changes you would only need to make a single edit to the entire workbook to modify it correctly, say, if you suddenly needed to calculated Vat at 21%, etc just by adding 21% to your dropdown list, and the column output would automatically change to 21 without you having to create a whole new column and set of formulae

euser1984

Fair play I'll try that so.... Will let you know when I get stuck. lol

euser1984

I was thinking while I'm working on this issue and it's for a local business person, because they were still using a calculator manually for their calculations. There aware that the problem was fixable with excel but just couldn't find anyone to do it, they did try an excel course (I don't know what kind of one though). I suppose the excel course wouldn't have gone into the likes of what we're working on here?

Has anybody tried to see if there are many potential customers in need for the likes of this service in more advanced excel? Maybe bigger business' anyway might be getting a full time accountant working on there needs.

euser1984

Overheal wrote: »

Vb? No. Lol.offshore freelancer sure I'll take a fiver in my steam wallet

Yep, the Indians have all the skills. They can do most programming jobs at a fraction of what somebody in Ireland would get paid; and, I mean a fraction!

Check out some of the freelancer sites like getafreelancer or something. Don't ever give the graphics design over to them though, because the're brutal at it.

You have these mad little companies in India where there are a couple of people working and they are doing stuff for the cost ofpeanuts here, but over there in India they are getting decent pay.

Post up a project you want done and set a budget, and people will check out your project, so they can decide if they want to offer to do it. Some of the freelancers have massive feedback (it's a bit like shops with loads of sales on ebay that have a 100% success rate). You can make a decision to take on a less rated bidder (on feedback amount) when you look at there projects completed and get pleasantly surprised to find there very good. People leave reviews on him on each project also.

Check it out if you don't believe me

srsly78

The thing is a (remotely working) Indian won't come to your company, set you up, and show you how to use everything.

There are lots of developers offering consultancy with this kind of stuff, you didn't look very hard it seems.

euser1984

srsly78 wrote: »

The thing is an Indian won't come to your company, set you up, and show you how to use everything.

There are lots of developers offering consultancy with this kind of stuff, you didn't look very hard it seems.

I didn't because all I knew is that nobody ever contacted her or she never saw an advert for such services to ask her if she had problems in this area.

Marketing issue then?

srsly78

More of a money issue on your side I suspect. Business consultants are expensive, it's not worth the hassle for a few hundred quid.

Most accountants know how to use spreadsheets, you can probably get one to sort you out. They charge lots too as I'm sure you know.

Thoie

If you don't want to get into VB, the formulae are pretty straightforward (IFs)

I'd do something like this:

RATE a = 13.50%
RATE b= 23%

Supplier Ref No Gross VAT rate Nett +VAT
1 2,000.00 a 1,762.11 237.89
2 2,000.00 b 1,626.02 373.98

Keep the rates at the top in named cells so that when the rate changes you can just change the values in there.

There are two IF formulae - one for the Nett and one for the +VAT

The Nett formula is (longhand) IF rate = a, then divide gross by 1+ lower vat rate, else if rate = b, then divide gross by 1+higher vat rate, else if neither a nor b, show an error.

The +VAT formula is then: IF rate = a then multiply the Nett by the lower rate, else if rate = b then multiply the Nett by the higher rate, else if neither, show an error.

Google the terms in italics, and that should be sufficient to get what you need. If you run into problems, throw up a sample spreadsheet.

tenifan

srsly78 wrote: »

The thing is a (remotely working) Indian won't come to your company, set you up, and show you how to use everything.

the indian's will do what you tell them to do. it's your responsibility to tell them what to do, and also to test what the indian's did against the expected result

Overheal

I meant ME lol but seriously we aren't talking about a complicated project here. This is piss easy excel stuff. This wouldn't even be assigned as a homework problem to me as a freshman engineer.

srsly78

Yes it's easy, but what about all the free ongoing support expected?

Overheal

I mean, it's a workbook, not a cash till

euser1984

Overheal wrote: »

I meant ME lol but seriously we aren't talking about a complicated project here. This is piss easy excel stuff. This wouldn't even be assigned as a homework problem to me as a freshman engineer.

Your not an engineer if your not an engineer. It's very annoying the way that the word is used so much to make opportunities sound more exciting and wow. It's good marketing though!

Overheal

euser1984 wrote: »

Your not an engineer if your not an engineer. It's very annoying the way that the word is used so much to make opportunities sound more exciting and wow. It's good marketing though!

Or, if you a Mechanical Engineering undergrad...?

euser1984

Overheal wrote: »

Or, if you a Mechanical Engineering undergrad...?

Nope...not an engineer. An engineer is someone that designs solutions to resolve problems....your just learning a load of stuff like computer programming, languages.

You wouldn't be able to solve any of the real world problems engineers face when trying to solve issues (with time constraints and limited budgets) and certainly wouldn't inspire any confidence to take someone like you on hand to design their new house. You wouldn't even be ready for internship at such a level.

Your basically out into the real world after living in your parents house for 18 years and having passed the leaving cert. Just because you have a really good memory and you get 600 points in your leaving cert. does not straight away grant you to such an entitlement as "engineer", or "king", "queen" whatever.

The word is also used incorrectly a lot of time in relation to the difference between "engineers" and "technicians" that fix computers.

A technician is not an engineer! grrrr that annoys me. The hr people don't even know the difference between the two and if they do, then the marketing dept. won't be too happy.

"BMW" "kia" "FERRARI" "lada"

That was me being nice by the way...It's important to know imo...that's how you meet the best people.

srsly78

LOL

You think software engineers don't "design solutions to resolve problems"? Engineer is not a protected title in Ireland anyway, get over it :P

euser1984

srsly78 wrote: »

LOL

You think software engineers don't "design solutions to resolve problems"? Engineer is not a protected title in Ireland anyway, get over it :P

Man, seriously, when it all comes down to it, nobody becomes an engineer the day they start their first year.

Overheal

euser1984 wrote: »

Nope...not an engineer. An engineer is someone that designs solutions to resolve problems....your just learning a load of stuff like computer programming, languages.

You wouldn't be able to solve any of the real world problems engineers face when trying to solve issues (with time constraints and limited budgets) and certainly wouldn't inspire any confidence to take someone like you on hand to design their new house. You wouldn't even be ready for internship at such a level.

Your basically out into the real world after living in your parents house for 18 years and having passed the leaving cert. Just because you have a really good memory and you get 600 points in your leaving cert. does not straight away grant you to such an entitlement as "engineer", or "king", "queen" whatever.

The word is also used incorrectly a lot of time in relation to the difference between "engineers" and "technicians" that fix computers.

A technician is not an engineer! grrrr that annoys me. The hr people don't even know the difference between the two and if they do, then the marketing dept. won't be too happy.

"BMW" "kia" "FERRARI" "lada"

That was me being nice by the way...It's important to know imo...that's how you meet the best people.

You are entitled to your opinion but you have no idea who I am or how old I am for that matter. Aside, we have a real life situation where oh my goodness, someone needs a basic excel workbook, and you think I need a bachelors degree for that? Get a grip.

euser1984

Overheal wrote: »

You are entitled to your opinion but you have no idea who I am or how old I am for that matter. Aside, we have a real life situation where oh my goodness, someone needs a basic excel workbook, and you think I need a bachelors degree for that? Get a grip.

I think you think I might be having a correct go at you, but that's not the case.
Sometimes my words come out the way they go through my mind, (and everyone is unique).
I also thought that this thread was about something different though now.

I think there are different schools of thought though on this one but I'm not going to argue because I have no idea who you are or what age you are. You could very well be a distinguished professor or hold a doctorate in an area like this. There are other schools of thought though.. http://en.wikipedia.org/wiki/Engineer_in_Training

Can you see my point of view though - again, I'm here on a neutral level...and don't intend to get involved with anything other than the topic this has turned into?

Overheal

by those definitions interestingly i dont think ill ever attain the EIT label as strictly speaking I will attend the ABET-certified school less than 3 years prior to graduation, while I transferred in with enough credits for junior with all the math and science and general education requirements. So I will receive my degree before then. /OT

euser1984

Overheal wrote: »

by those definitions interestingly i dont think ill ever attain the EIT label as strictly speaking I will attend the ABET-certified school less than 3 years prior to graduation, while I transferred in with enough credits for junior with all the math and science and general education requirements. So I will receive my degree before then. /OT

Don't compare apples with oranges man.

There is a difference between thinking creatively to solve an issue and engineering a solution....

If you get a lego set and can design loads of new things and you become the best in the world at it....does that make you an engineer?