I want to calculate the sum of a column but I have 4 lots of figures going down the column and I only want it to calculate the some of 2 of those 4.
The 4 figures: £4.80, £3.80, 31 & 25.
These 4 figures are within the column multiple times and I just want to have one cell that sums up the £3.80's and £4.80's within the column and then another cell to do a separate sum of the 25's and 31's.
Is this possible, if so how? Does it require an IF SUM formula of some sort?
In the attached workbook, see the "P & L" tab, cells I30 to I48. The idea of the formula I have placed into these cells is to drag out the figures from column I in the "Trial Balance" tab of the workbook. A similar formula is used succesfully in the "balance sheet" tab in Column B. I don't understand why the formula doesn't work in the cells i've specified in in the " P & L" tab it seems to work for I8 and the rows immediately below it "revenue".
I have 2 spreadsheets, one is on crew costs and the other one is a summary page. On "crew cost" worksheet, say A1= IBM, B1 =Microsoft, C1=HP and D1=Pioneer. I want to link A1 to D1 to cells A1 to D4 on "summary page".
However, I do not want to use "paste special", "transpose" function as I may need to add in columns to "crew cost" spreadsheet.
Instead I hope that I could link A1 on "crew cost" spreadsheet to A1 on "summary page" spreadsheet. Then I hope to drag the formula from A1 down to D4. The result will be A1= IBM, B2 =Microsoft, C3=HP and D4=Pioneer on "summary page" spreadsheet.
However, it does not work as dragging only occurs through columns or through rows. Is there any formula that I can use?
I am working with percentages, and trying to avoid #DIV/0! Errors at the same time. Lets assume column A holds last years figures, and column B holds this years figures, column C calculates the difference in percentages.
I am having dificulty where say A1 = 0 B1 = 10 (C1 Should be 100%) then what happens if A2 = 5 and B2 = 0 (C2 Should be - 100%)
Is there a common formula that will account for all possible + or - figures in A and/or B that will also suppress #DIV/0! Errors?
I need to round up the sum of a column figures to the next nearest quarter. For example: $1.11 would be rounded to $1.25, or say $1.83 would be rounded to $2.00. What formula would I need to do this? I'm using Excel 2000
I am using Excel 2007, I have a customer sheet and a sales sheet in the same work book. My customer sheet lists all customers with a single entry only each with a unique customer code. My Sales sheet lists at an item level but does include the customer code so if my customer has purchased more than one item the customer code can be repeated on the Sales sheet on multiple rows.
When I do a vlookup to display the amount each customer has spent on the customer sheet, only the very first sales figure is displayed, if further down the sales sheet the same customer has made another purchase I want this figure to be added to the first figure and all I want to see on my customer sheet is the final total figure.
The vlookup formula I am currently using is: =VLOOKUP(G2,Sales!$A$2:$B$1785,2,FALSE),
I'm thinking maybe a Vlookup will not do what I require, maybe I need a different formula/function?
attached is a returns sheets that a qty is logged against part number every week. Some of the data I can use pivots on ( no problem ), but I have an issue on the returns tab where I think index/match / sum or something else will be better. At the moment because of the layout of data I have to drag each week number into the pivot value, do a greater than zero filter then summarize as a sum (yuk) I just cant get my head around index/match /sum. It would be great just to be able to enter a week number, and all the warehouse, part numbers and qty came through. There is a short explanation on the returns tab of what im looking for.
so in essence I want week numbers across the top, but be able to enter a year on the returns tab that looks at the week number. I don't know enough about the formula to change.
I have a worksheet with monthly sales figures by associate and by store. The store has a monthly goal as do the associates. If the store hits it's goal then the overall sales total is multiplied by 1% and then divided by the percentage of each associates involvement to reach that goal. (ie...150,000*1%=1,500, John sold 35,000=23%, so John gets $345 extra commission). If Johns goal was $25,000 and sold $35,000 he gets 1% or $350 commission. In turn, if he meets 1 or both sets of criteria those will be added together. If he doesn't meet either one then the result is Zero.
I have the store goal and Johns goal in separate cells to reference against. The actual sales cell is formula based.
This is basically what i'm trying to do: If criteria 1 is met then % of 1% of store goal, if criteria 2 is met then 1% of individual goal, if both are met result1+result2. if neither is met then zero. I think?
I have a spreadsheet for payroll. The last column is for net pay. I enter this by hand when I get it back from the payroll tech. There are no formulas in this column. When done entering these figures, I click on "save". Then I close the file. From past experience with this sprdsht, I have found that it doesn't always save this column. It saves all the other info. I have entered in the sprdsht, but not this column. So, I have to re-enter everything in this column and "save" again. Close the sheet, then re-open it to be sure it got saved. Why won't it save this column of info. the first time?
I receive monthly expenditure returns from different departments which I have to consolidate. The problem I'm encountering is that some departments submit their data as a monthly figure and some as the cumulative position. It would be useful if I had a formula that identified what data type was submitted and from that calculate both the monthly and cumulative figure.
If you look at the attached example I'd like to input a formula in columns I and J that uses the data contained in columns B to E to calculate the monthly and cumulative expenditure figures.
I have a small problem that I may be able to deal with in another way, but if all else fails then I need some assistance.
I have this formula: =AND(N3<=$U$1,MOD($U$1,N3)=0)
But unfortunately there will be a time when cell U1 will have the contents of perhaps 12(1). Is there any way to get the formula to ignore the brackets and the figures in the brackets?
I need to indicate if the figures for this week has gone up or down compared to last weeks figures.
So looking at the attached, I need to add a column (I5) to sheet Admitted Patients WE 20081005, so it compares the % treated in <18 wks figure by specialty to previous weeks figures on sheet Admitted Patients WE 20080921.
Ideally I would love an arrow either point up if the figures has increased or down if they have reduced.
I need to produce three different formats of accounts data monthly. I attached a file with each accounts description in Columns A, E and G. Is there a way to match the data with those accounts name in a faster way than manual linking?
I am trying to make an inventory cover page - that shows data of separate products on different sheets with the following;
Product Date Previous Total Amount Taken New Total and occasionally there is a Re-issue of stock to the previous total.
This is at the moment an historical record as anyone can see the usage across the dates for any particular product by looking at the sheet. Each time there is a change to the inventory it is recorded on a new row. But what I am trying to do is to copy the last row of data from each sheet to the cover page to show an overall inventory.
The part where I get stuck is making the selection of the last entered row of data [from each sheet] automatically updating the coverpage, when each sheet will get new data added frequently.
I thought about somehow making a duplicate of the last row and locking it in place and linking that to the coverpage - but I still do not have a clue on how to get it to automatically select the last row of the data.
.Offset(3, 0).Value = "For " & P & " numbers there are " & Format(tly, "###,###,##0") & " x different values of " & cmb & " numbers. " ... which includes figures upto and including millions.
The thing is that the above code ONLY produces figures that are relevent.
I have a large number that has 6 figures.I want to display this number to 3 significant figures on the spreadsheet, but I have more formulas that need to use the unrounded number.So is there a way to DISPLAY the number to 3 sig figs whilst keeping it as the complicated number for the rest of the formulas?
I'm wanting to Rank a set of figures in one column but use criteria in another column to pick up the array which is used. I don't want any figures to be ignored so will have multiple rank 1's 2's etc. Would also be handy if I could use multiple criteria.
I am using a Hlookup to retrieve Words and Figures but when retrieve words it is failed and output is figures ONLY while I need the same word to be shown in the cell value?
I have a list of 30 numbers representing receipts due. I have one receipt which I know must represent the sum of some of these numbers. Is there anyway in Excel that I can highlight the range of 30 numbers and ask Excel to sample all possible combinations to find which numbers add up to my total receipt?
I have an excel database with about 5 columns and 150 rows whose cell data I want to display to 2 significant figures. Almost all the data is numeric, but there are one or two cells with text in them, and the numeric data varies between 1 and 4 significant figures at the moment, some >1, some
Im trying to find a way to script a button to delete entire rows from a selection that contains rows which total 0. It seems like an easy task but ive drawn a blank.
I'm trying to find a way of displaying results from a worksheet which can range from a few thousand to several million. However I only want to report to a set number of significant figures(5), i.e. if the result is say £25,256,128 I would like to show the resuilt to the first 5 significant figures i.e. £25,256,000, and say £3,508,569 as £3,508,600.
