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