I am using excel as an income and expense book. In this book I have a column for GST which is Goods and Services Tax. So I have put in a calculation in the GST column like total $cost / 11 = GSt amount. My problem is some items are GST free, how do I go about putting in a formula which will only calculate gst on certain categories.

- CALCULATE GST AMOUNT FROM AMOUNT IN OTHER CELL
- Embedded if formula
- How to calculate GST price so that the final selling price will be a whole number?
- Profit & GST Return Calculation Program
- Payment Expense Spreadsheet With Gst
- Formula to calculate multiple gross profits required
- USD $10.00 Inputting Formulas Into Userform Calculator - Relatively Simple
- Seeking excel formula that calculates lease and balloon payments
- Help with incl GST column
- GST formula - Rookie needs help !
- Locking and hiding the formula calculation area of worksheets
- Random Cells Not Calculating?
- Calculating average from different workbooks
- Use two different calculations based on ID number in column
- Form that calculates and shows the total price of the products selected in an order
- VBA macro using calculated value out as new input in.
- Changing VBA code to work with calculate
- Calculate totals based on three criteria
- Hourly Calculation in Excel 2010
- If statement formula to calculate when there is text involved.
- Time calculations
- Formula to calculate most common trifectas in a data series
- Search for Date and Output Calculations onto Separate Sheet
- Calculating Distance between many points

component of an amount (which includes gst) in cell A1? Would appreciate your

help.

Novice excel user

I had to add the cash column. In column H I used to have the following formula

=IF($E5="y",$F5*$C$1,$A$1*0)

That worked like a treat

(C1 had the GST component and A1 had a 0)

Now I want to say, if there is an amount in column F or when there is an amount in column G calculate GST

I tried =IF($E4="y",$F4*$C$1,$A$1*0)=IF($E4="y",$G4*$C$1,$A$1*0)

but the result is False.

I know I do something small wrong, please help

Thanks

I know programming but new to excel. I want to make one excel file for

calculating profit/loss and GST Return for my home based business.

My requirements are really simple. I have listed out in the attached file.

May be some one who knows excel can make it within 15minutes.

Can anybody help me making this application.

Thanks,

I originally posted this in the Software Needed forum in reply to (http://www.ozgrid.com/forum/showthread.php?t=33077) but it does not seem to be showing up. I would appreciate any help with a suitable formula for my problem:

Hi

I used erecord the other day to do my activity statement for the first time (I have just started a home based business which has not yet started trading but I had to send the BAS for the purchases that I had made for the business) and it was quite easy to use and you can send it electronically to the ATO which saves a lot of hassle particularly as I am very not accountancy litterate.

However I am trying to develop an expenses/payment spreadsheet similar in function to erecord but that allows me to categorise the inputs and I am stuck with a formala so I wondered if you might be able to help me out.

My headers are:

Date

Cbookref = (drop down validation box) similar to a chart of account #

Category = ie - advertising accounting fees etc.. uses a look up function with cbook ref to populate field

Description

Amount

GST Type # = (drop down validation box) a number from 0 to 8 to use as lookup up for GST type

GST Type = uses a look up function with GST Type # to populate field (Capital GST-free , Capital with GST) see below

Private % = private use percentage

Private value = Amount * Private %

Net = Amount - Private value

GST = Net/11.

There will be other headers the right to represent the exact expenditure (office equipment - postage etc...) which will enable me to keep track of individual totals.

This is the GST list:

0 GST Type (header)

1 Capital GST-free

2 Capital with GST

3 Capital for input taxed

4 Excluded

5 No GST in the price

6 Non-capital GST-free

7 Non-capital Input Taxed

8 Private use acquisitions

This what I would like to do:

I'd like to be able to cut out the Private Value cell and do the one calculation in the NET cell:

If GST Type # = 1 (Capital GST-free) and Private use = 0% then Net = Amount

If GST Type # = 2 (Capital with GST) and Private use = 20% then Net = calculate Profit % (this could be any number according the private usage) and then take it away from the Amount cell

and so on according to the selection made in GST Type.

I hope that that makes sense, please let me know which would be the best function and formala to use.

Thank you in advance for your help.

Jean

a = DESCRIPTION

b = PURCHASE QUANTITY MLS

c = BUY PRICE

d = GP RATE PER PURCHASE UNIT ONLY

e = COST PLUS GP GST AMT 10%

f = TOTAL INCL PURCHASE PRICE GP AND GST

g = SERVING SIZE 1 % GP (AS THIS IS CHANGED I NEED THE PRICE TO AUTOMATICALLY RECALCULATE ON THE PRICE, ALL THE SERVING SIZES HAVE DIFFERENT GP'S, HENCE THE MULTIPLE COLUMNS).

h = SERVING SIZE 1

i = SERVING SIZE 1 COST PLUS GP PLUS 10%GST

j = SERVING SIZE 2 % GP

k = SERVING SIZE 2

l = SERVING SIZE 2 COST PLUS GP PLUS 10%GST

m = SERVING SIZE 3 % GP

n = SERVING SIZE 3

o = SERVING SIZE 3 COST PLUS GP PLUS 10%GST

p = SERVING SIZE 4 % GP

q = SERVING SIZE 4

r = SERVING SIZE 4 COST PLUS GP PLUS 10%GST

I would really appreciate someone's urgent help on this one as the boss is waiting for a miracle - all credit will be allocated where due! Julie

I had someone create a Excel Spreadsheet for me with a userform for calculating something for me.

Everything is in completion EXCEPT for the formula's represented by the particular fields in the userfield.

Since I'm not THAT familiar with VBA I was wondering if someone could help input the formula's for me. I have the formula's on hand and exactly where they need to go, I just don't know how to do them via VBA.

Any help would be appreciated and if it helps the thought process at all the calculator is based off of: http://www.baileycapitalfund.com/Lea...calculator.php.

Please look below if you can help (the red highlighted portion will be the user input and the blue highlighted portion will be the output after "calculate" is pressed):

[MOD EDIT]...... removed image

**Please note the difference between "Tax Rate (GST/PST)" and "Tax Rate" below, they are both different user inputs, I apologize the person who created the userform made it a bit confusing**

Cost of Equipment (Cash, Loan, Lease)= Equipment Cost (User Input)

Bank Interest (Loan)= (Cost of Equipment * 0.032)

Bank Interest (Cash & Lease)= N/A

Monthly Lease Payment Before Taxes (Cash & Loan)= N/A

Monthly Lease Payment Before Taxes (Lease)= ((Equipment Cost/(Preferred Term *12)) * (1+(Lease Rate Quoted/100)))

Taxes (Cash & Loan)= (Equipment Cost * (Tax Rate (GST/PST)/100))

Taxes (Lease)= (Monthly Lease Payment Before Taxes * (Tax Rate (GST/PST)/100))

Lease Buyout (Cash & Loan)= N/A

Lease Buyout (Lease)= (0.1 * Equipment Cost)

Total Equipment Cost Before Tax Savings and Future Value(Cash, Loan & Lease)= Equipment Cost (User Input) + Taxes + Bank Interest

Depreciation (Cash & Loan)= ((100%-(Lease Amount Buyout Rate))/100)*(Equipment Cost)

Depreciation (Lease)= N/A

Bank Interest (Loan)= (Cost of Equipment * 0.032)

Bank Interest (Cash & Lease)= N/A

Lease Payments Plus Buyout (Cash & Loan)= N/A

Lease Payments Plus Buyout (Lease)= Total Equipment Cost Before Tax Savings and Future Value

Total Deductions (Cash)= N/A

Total Deductions (Loan & Lease)= Depreciation + Bank Interest + Lease Payments Plus Buyout

Deductions x Tax Rate (Cash)= N/A

Deductions x Tax Rate (Loan & Lease)= ((Tax Rate/100) * Total Deductions)

Interest Earned On Capital Over Term (Corporate ROI) (Cash, Loan & Lease)= (Cost of Equipment *0.08)

Total Equipment Cost (Cash, Loan & Lease)= Total Equipment Cost Before Tax Savings and Future Value

Less Corporate Tax Savings (Cash)= N/A

Less Corporate Tax Savings (Loan & Lease)= Deductions x Tax Rate

Less Interest Earned (Cash, Loan & Lease)= Interest Earned on Capital Over Term

Less GST Tax Credit (Cash & Loan)= (Equipment Cost * 0.05)

Less GST Tax Credit (Lease)= (Equipment Cost * (Lease Rate Quoted/100))

Net Cost of Equipment (Cash, Loan & Lease)= (+(Less Interest Earned) + (Total Equipment Cost))- (Less GST Tax Credit)

Cash On Hand At End of Term (Cash)= N/A

Cash On Hand At End of Term (Loan & Lease)= (Cost of Equipment + Interest Earned on Capital over Term) - Bank Interest

[MOD EDIT] - Removed attachment

instalment, then separate the instalments into the interest, and GST

components. Then calculate the balloon payment due at the end of the leasing

period

I only want the gst column to total wages if the gst column is 0, and include gst if the fst column is 10.

How can I get this working

....dan

im trying to figure out a very simple formula im sure...

i have all the totals from my year end... and am t rying to figure out an easier way to break down the gst/cost of goods without pulling up each receipt.

what im wondering, is what would the forumla be, to calculate the initial cost of something... and then breakdown the gst from that.... with having ONLY the final cost ?

It looks rough and I want that the user can not go or see the lower rows after row82 and neither go beyond column L.

So the scroll area should be A1:L82.

Bearing in mind that this hidden area has a lot of vital info on which the working area calculations depend.

I tried couple of VB codes from this forum but nothing seems to work for me.

someone help!

All was well and working very good.. and then;

I wanted to make it so the "omits adjacent cells" error wouldn't show, so i set it to not check for errors.

I also wanted to make it so she could only enter things into certain cells to save her wiping formulas, so i selected all the cells i wanted to unlock with intentions of protecting it later.

I also wanted to make it so she can't see the formulas, so i selected everything and checked the "Hidden" box, assuming it will only bother hiding formulas from the formula box... (this is where I think I might have done something wrong)

I went on to remove all the dummy data and realised i had left one cell displaying a zero (i'd rather it display nothing) so I change the formula from =Leads!A1 to =IF(Leads!A1"",Leads!A1,"") - normally this would work fine, but instead it just displayed the formula.

I went back and unticked hidden for everything and locked all the cells again, and its still not calculating. even when i put something in "Leads!A1" that cell does nothing... I promised I'd have this to her tomorrow And I'm worried I've screwed something up I've also tried exiting & opening it back up.

The other cells seem fine, but I'm scared to mess around too much.

Every month I have to calculate averages from a workbook that is updated on a daily basis.

C:/User/Me/Reports2012/June 2012/

This folder above has all the reports for this month except Fridays and I have folders from Jan to Dec, each folder contains reports for that particular month except for Friday.

The files are named DailyReport DDMMYY.xlsx

I want to calculate the average of

Columns: D, F, H, J, L, N, Q, T, V, X, Z, AB, AD, AF, AH, AJ, AL, AM, AQ, AT, AW, AZ, BC, BF and

Rows: 9, 17, 25, 33, 41, 49, 57, 73, 81, 89, 97, 106, 114, 122

I mean is I want to calculate average of D9, D17..... D122

F9, F17, ... F122

Until BF9, BF 17, .... BF 122.

From all the workbooks’ Sheet2

Is it possible or should I just copy and paste the information onto one worksheet and then calculate the average?

In essence I need to loop through column D, check which type of ID number is present, and then enter the correct formula in column E.

I use Excel 2003 and I have some experience with macros but I am totally new to forms.

I would like some guides to design a form that calculates and shows the total price of the products selected in an order as follows:

Each product is identified by:

1) a label,

2) a checkbox,

3) a listbox for the Quantity,

4) a textbox for the Price.

- The product unit prices used in the form are stored in a table.

- When a product is checked the form shows "1" in the Quantity listbox and the calculated product price (quantity * product unit price) in the Price textbox.

- When a product is unchecked the form clears both Quantity listbox and Price textbox.

- If the user modifies the Quantity of a checked product the form shows the new calculated price (quantity * product unit price) in the Price textbox.

- The user can also modify the calculated price by entering a value in the Price textbox.

The total price of the order is shown in a textbox adding the PRICE textboxes of all procucts.

By pressing a command button "PLACE ORDER" the user records all fields of the order in a table.

By pressing a command button "CLEAR FORM" the user clears the form.

Suggestions will be greatly appreciated.

Regards,

Didier

I am trying to write a VBA macro that will mimic what i have already done in an excel worksheet i have made. The excel worksheet uses inputs for a heat exchanger and finds the temperature as it leaves the heat exchanger. This temperature is then the new input for the second line of the worksheet and it changes quite a few of the values in the sheet. I have the excel worksheet working where you just drage the row down and it does it automaticly, but i would like to have a VBA macro that all the values can be input in with the number of cycles until the heat exchanger equalizes out.

I am able to get the macro to work when it calculates just the first cycle, all the equations work my variables are ok for just one time. What i don't know how to do is make my program realize that it has to use the calculated temperature out of the heat exchanger as my new input for the heat exchanger. I know how to do it once so it is more of a syntax or understanding of a loop or an array for the values that are changing.

Thanks any help is appreciated.

VB:Thanks.Range) Sheet5.Unprotect Password:="password" Select Case Target.Address Case "$M$2" ActiveSheet.ChartObjects("Chart 1").Chart.Axes(xlValue) _ .MaximumScale = Target.Value Case "$M$3" ActiveSheet.ChartObjects("Chart 1").Chart.Axes(xlValue) _ .MinimumScale = Target.Value Case Else End Select Sheet5.Protect Password:="password" End SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

I'm trying to calculate a series of numbers based on three different criterion. My problem seems to be that when I reference the first "current month" cell it sees it as a string instead of a date. Here's what I'm trying to do:

Calculate numbers that belong to a certain employee id.

Calculate those numbers between the 1st and the 15th of the month, and then again (in a different cell) from the 16th to the EOM. Let's use emplyee 4405 as an example.

Ex: Sum the earned column if the employee id is 4405 and the date is between the 1st and the 15th.

My "current month" start date is auto-populated from another sheet in the workbook and the subsequent cells are simply using the +1 copy. I'm just using it on this sheet to act as a reference point for my codes. All of the other data is retrieved from a remote database and refreshed when the workbook is opened or the dates in the other worksheets are changed:

Date Sold Earned Employee ID Month 7/1/2012 4.45 4405 7/1/2012 7/4/2012 5.27 4225 7/2/2012 7/5/2012 14.75 1522 7/3/2012 7/8/2012 86.32 4405 etc. down to 31 7/12/2012 14.15 4405 7/15/2012 32.60 1522 7/18/2012 88.90 2335 7/24/2012 52.15 4405 7/30/2012 14.20 4423

Thanks very much for any help on this!

Following things are from a game and I want to calculate what's the best things to use

Buildings make cash for me

Building 1 makes 80 coins every 2 min

Building 2 makes 120 coins every 5 min

Building 3 makes 160 coins every 8 min

Building 4 makes 200 coins every 12 min

Question is, how can I make excel calculate it on an hour and 24 hourly base?

How would I put these calculations into Excel. I figured it out with other stuff, but they were hourly bases

Building 1 makes 600 coins every 4 hours, that's 3600 in 24 hours. In Excel that would be =(D3/B3)*24

D3 being the 600 B3 being the 4 hours

I hope this is allowed to be asked on this forum, as it's not the normal kind of Excel question I suppose.

Thanks for any help!

=((D9-C9)+(F9-E9)+(H9-G9)+(J9-I9)+(L9-K9)+(N9-M9)+(P9-O9))*24

if one of those cell have the text "off" in it I still want it to finish up the formula and calculate the total hrs worked.

Basically what this does is calculate total hrs worked for the week. Right now if i want to show an employee off I would just leave the day blank but I have been requested to put the word "off" in for the day. Currently my time in and my time out cant equal the same so I basically be showing under the time in cell to have the word "off" and time out be blank.

Thanks for any help.

for example : i came to office at 8.45 am and i go from office at 1.20 pm

how can i know how much time i spent in my office

and also i need all the formulas regarding time calculation

I have collected race results for several race meetings over several months. I would now like to analyse the data to determine the most common trifectas, (series of 3 runners) from race results (in sets of 3 numbers for each race, 1st, 2nd and 3rd).

Eg. Race Results Data below.

HORSHAM, 28/06/12 SANDOWN PARK 28/06/12 GEELONG 28/06/12 1st Place 2nd Place 3rd Place 1st Place 2nd Place 3rd Place 1st Place 2nd Place 3rd Place RACE 1 6 8 4 5 4 6 5 1 8 RACE 2 8 4 3 5 2 7 8 2 1 RACE 3 8 4 2 8 6 1 8 1 3 RACE 4 7 3 4 8 3 2 1 5 8 RACE 5 8 3 4 1 2 6 8 5 7 RACE 6 3 5 1 8 4 5 8 6 5 RACE 7 5 2 3 8 4 5 7 1 3 RACE 8 6 3 5 7 3 6 8 4 5 RACE 9 3 8 4 8 3 7 3 6 7 RACE 10 1 5 6 3 5 4 5 1 7 RACE 11 1 5 8 5 4 3 6 2 1 RACE 12 7 3 5 6 5 1 7 8 1

I have been able to work out manually that in the above example,at Horsham 28/06/12 the combination 8, 4, 3 comes out 3 times. I would like suggestions as to how to calculate this using Excel for each race meeting and also overall the data. Knowing the MODE or the COUNT doesn't help because it doesn't account for the combination of numbers that come up regularly. I would also like to be able to distinguish between the different permuations, that is, 3, 4, 8 = 8, 3, 4 = 8, 4, 3 = 3, 8, 4.

There are currently over 200 columns of data by 12 rows to which I am adding to daily.

I am using Excel 2007.

If anyone can offer any help, that would be greatly appreciated.

Regards

So far I've gotten the code to output the station code and year.

The averages output as well but some reason they are slightly off.

And the month portion doesn't get past Month 12 (December).

I tried using select case but it didn't work out.

Also, if its possible to add in a function that reads in the .txt file rather than copy and paste the txt file into Excel.

Some of the data .txt files are larger than Excel capacity.

I've comment out the slightly working portion ,so I could focus on the months.

For Ex:

Station Code Month Flow(cfs)

02429900 June 1973 -----

02429900 July 1973 -----

Station Code Annual Flow(cfs)

02429900 1973 ------

02429900 1972 ------

etc.

VB:If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

VB:AVGs() 'Range("C28").Select 'Dim i, j, k, h, m As String 'i = DatePart("yyyy", ActiveCell) 'j = -1 'k = 0 'm = 0 'Do ' j = j + 1 ' If DatePart("yyyy", ActiveCell.Offset(j, 0)) < i + 1 Then ' k = k + ActiveCell.Offset(j, 1) ' Else ' End If 'Loop While DatePart("yyyy", ActiveCell.Offset(j, 0)) < i + 1 ' h = k / j ' ThisWorkbook.Sheets("Sheet2").Range("F2").Value = h ' ThisWorkbook.Sheets("Sheet2").Range("E2").Value = DatePart("yyyy", ActiveCell.Offset(j, 0)) ' ThisWorkbook.Sheets("Sheet2").Range("D2").Offset(m, 0).Value = ThisWorkbook.Sheets("Data").Range("B28").Offset(m, 0).Value ' ' 'Do ' If DatePart("yyyy", ActiveCell) = i + 1 Then ' ' i = DatePart("yyyy", ActiveCell) ' j = -1 ' k = 0 ' Do ' j = j + 1 ' If DatePart("yyyy", ActiveCell.Offset(j, 0)) < i + 1 Then ' k = k + ActiveCell.Offset(j, 1) ' Else ' End If ' ' Loop While DatePart("yyyy", ActiveCell.Offset(j, 0)) < i + 1 Or IsEmpty(ActiveCell.Offset(j, 0)) ' h = k / j ' m = m + 1 ' ThisWorkbook.Sheets("Sheet2").Range("F2").Offset(m, 0).Value = h ' ThisWorkbook.Sheets("Sheet2").Range("E2").Offset(m, 0).Value = DatePart("yyyy", ActiveCell.Offset(j, 0)) ' ThisWorkbook.Sheets("Sheet2").Range("D2").Offset(m, 0).Value = ThisWorkbook.Sheets("Data").Range("B28").Offset(m, 0).Value ' Else ' ActiveCell.Offset(1, 0).Select ' End If 'Loop Until IsEmpty(ActiveCell.Offset(j, 0)) Range("C28").Select Dim n, o, p, q, r, x As String n = DatePart("m", ActiveCell) o = -1 p = 0 r = 0 Do o = o + 1 If DatePart("m", ActiveCell.Offset(o, 0)) < n + 1 Then p = p + ActiveCell.Offset(o, 1) Else End If Loop While DatePart("m", ActiveCell.Offset(o, 0)) < n + 1 q = p / o ThisWorkbook.Sheets("Sheet2").Range("C2").Value = q ThisWorkbook.Sheets("Sheet2").Range("B2").Value = Format(ActiveCell.Offset(o - 1, 0), "mmm yyyy") ThisWorkbook.Sheets("Sheet2").Range("A2").Offset(r, 0).Value = ThisWorkbook.Sheets("Data").Range("B28").Offset(r, 0).Value Do If DatePart("m", ActiveCell) = n + 1 Then n = DatePart("m", ActiveCell) o = -1 p = 0 x = 0 Do If DatePart("m", ActiveCell.Offset(o, 0)) < n + 1 Then o = o + 1 If DatePart("m", ActiveCell.Offset(o, 0)) < n + 1 Then p = p + ActiveCell.Offset(o, 1) Else End If Else End If If DatePart("m", ActiveCell.Offset(o, 0)) > n + 1 Then x = 2 Do o = o + 1 If DatePart("m", ActiveCell.Offset(o, 0)) = 12 Then p = p + ActiveCell.Offset(o, 1) Else End If Loop Until DatePart("m", ActiveCell.Offset(o, 0)) < 12 Or IsEmpty(ActiveCell.Offset(o, 0)) Else End If Loop While DatePart("m", ActiveCell.Offset(o, 0)) > 0 Or IsEmpty(ActiveCell.Offset(o, 0)) x = 0 q = p / o r = r + 1 ThisWorkbook.Sheets("Sheet2").Range("C2").Offset(r, 0).Value = q ThisWorkbook.Sheets("Sheet2").Range("B2").Offset(r, 0).Value = Format(ActiveCell.Offset(o - 1, 0), "mmm yyyy") ThisWorkbook.Sheets("Sheet2").Range("A2").Offset(r, 0).Value = ThisWorkbook.Sheets("Data").Range("B28").Offset(r, 0).Value Else ActiveCell.Offset(1, 0).Select End If Loop Until IsEmpty(ActiveCell.Offset(o, 0)) End Sub [B] [/B]If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Any assistance would be much appreciated

Thanks

I'm looking for code or just formulas that can save me time in finding the distances. Let me know if you need anything else

Thanks in advance

No luck finding an answer? You could always try Google.