Oops! Hi and welcome to OzGrid!
Is VAT calculated before or after the Postage is added in?
I know the basics of excel but not much more than that. I need to create a spreadsheet that will calculate the cost of items i wish to sell.
I need It to add together the item cost, gift cost and give a total. Then add the total to the postage cost and add VAT. So that I can then see the total amount that the item is costing me to work out my sale price.
Im sure this is a very basic thing to do but not to someone who doesnt know alot about excel!
You help is much appreciated.
Last edited by joannestevenson; October 22nd, 2005 at 08:50.
I thought the attached might be of interest to you and any others who need help in this area. There are two worksheets: 1 with VAT calculated before Postage and 1 with VAT calculated after Postage.
The formulas in Columns C, E, F are currently copied to row 15. You can copy them down as far as you want even though there is no Item Cost or Gift Cost entered.
The formula in column C (Total Cost) uses the COUNTA() function which counts the number of cells that contain data in the referenced range. So unless there is at least 1 cell that contains data, the formula will not calculate.
The formula in column E (VAT) doesn't calculate if the Total Cost has not calculated. I've used the ISBLANK() function to check is Total Cost is blank. So if VAT is calculated before Postage, the formula is =IF(ISBLANK(C2),"",C2*0.17). Alternatively, I could write the formula
=IF(C2="","",C2*0.17) or reversing the logic =IF(C2<>"",C2*0.17,"")
The formula in column F (TOTAL) doesn't calculate if the Total Cost has not calculated. So the formula, regardless of when VAT is calculated, is =IF(ISBLANK(C2),"",SUM(C2,D2,E2)).
So by ensuring that formulas only calculate when you want, you avoid all those 0s. Alternatively, you could use Excel's Conditional Formatting in Columns C, E, and F so that if the result is 0, the font is white so the 0s don't show. Go to column H on the VAT Before Postage worksheet where I have applied conditional formatting (select the cells and from the menu, Format>Conditional Formatting...). I have entered some numbers but only those <> 0 are shown because of the font colour.
There are currently 1 users browsing this thread. (0 members and 1 guests)