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.
Normally, when we use the =MAX() function, figures / numbers will appear. Is there any formula that can show the row the highest data is in? Let's just say that the figure is 3080 and it is under 'Transportation' row. Generally, when I apply =MAX() fx, 3080 will appear. Is it possible for it to show Transportation by using formula instead?
View 4 Replies View Related[URL]
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.
Example6.xlsx .i just cant remember what it is im meant to be using as i havnt done it since school. basicly on the attached spreadsheet, there are 2 tabs. the common column is Contract number, if the data in the contract number column matches then i need the figures for 20/40/TEU to show against the Special aggreement by code tab.
View 3 Replies View RelatedI 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 would like the last 6 figure in cell a1 to be highlighted cut and pasted into B1 - the amount to be cut will vary
View 4 Replies View RelatedI 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.
Dim figure from the cell b4,b5,c4,c5 to accumulate add total b11,b12,c11,c12, respectively.
View 5 Replies View RelatedHow to make space between the figures ex:11480831525 i want like: 11480 831525
because i want to do it for thousand of transactions in one time by using only one formula.
I have the following ...
.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 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?
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?
View 6 Replies View Relatedhow can I total three columns together, one with the value of #N/A, and two with figures in, without the total coming back as #N/A ?
View 10 Replies View RelatedI'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.
View 9 Replies View RelatedI 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?
View 2 Replies View RelatedDATE
PRODUCT
BALANCE
1/2
KEY CHAIN
4
1/2
KEY CHAIN
8
1/2
KEY CHAIN
27
[code]....
How to get final figure each product from sample data above:
the result:
Key chain = 27
Gift = 4
Hamper = 1
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 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?
View 9 Replies View RelatedI 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
View 9 Replies View RelatedIm 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.
View 5 Replies View RelatedI'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.
View 4 Replies View RelatedI have been trying to find a way to take the result of a formula, calculate how many decimal places based on significant figures, then rewrite the formula with the Round function.
=(5-2)/2
result is 1.5 taken to 3 significant figures is 1.50
=Round((5-2)/2),2)
I need to keep the formula in the cell for validation purposes and I need the result truncated to significant figures for accuracy. I am currently using the significant figure formula found on the forum for my data with numbers.
I am doing a budget sheet in Excel for the different expenses accrued by the different classes we offer in our university department (honoraria for guest lectures, tickets to shows or museums, etc.). The sheet is formatted as a table and I have the Total Row enabled at the bottom.
However, we have a reserved budget for different things. For instance, Fall 2013 museum tickets has, say, a budget of $1,000. Fall 2013 honoraria might have a budget of $2,000. I want to be able to filter to see only Fall 2013 honoraria: the totals row will reflect this, but I also want it to automatically tell me how much money is left in that budget (i.e. I want it to know to subtract $2,000 from the totals figure because that is the budget for Fall 2013 honoraria).
It gets a little more complicated because we also have a budget for each class too. One class might have both honoraria for guest lectures and field trips to a museum, and we might say "We don't want this class to spend more than $500." Again, I want to filter to see just this class, and have the Totals row also show me how much money is remaining for this class.
i am currently constructing a table on excel and need it to grab a figure which is constantly changing from another worksheet.
View 2 Replies View RelatedI have a late report due @ work.... I have people who have worked for 4 days to get me numbers i need and they are not right.....
Anyways I have a spreadsheet with a whole lot of numbers on it... I need to add 30% to each number. What is the easiest way to set this up.
I'm trying to come up with a formula that calculates a year to date target, taking into consideration the actual working days and the target for those days.
So for instance today is the 12th of May and my year to date percentage is 40% but it should be 100%. How do i create a formula to calculate that?
I have this formula in a cell:
=IF(AND($D4=H4,$F4=I4),3,IF(SIGN($D4-$F4)=SIGN(H4-I4),1,0))
but i need to somehow incorporate a formula that only shows this if a cell has a value present.
I know as an example its something like:
=IF(C2="","",C3)
but i dont know how that would go in my existing formula as i dont know how to stack IF statements?? (a very kind person on this forum did the original formula btw)
the cell that i need to have as the 'blank or not' is D4.
How to round to significant figures using excel, its for a question in one of my college assignments and i cant seem to find any information.
View 1 Replies View RelatedI am in the process of creating a spreadsheet to document employee holidays taken over 2014. Each month is shown on a different worksheet with a 'Total' column on the far right which calculates the number of holidays (represented by 'H') taken by individual employees. The formula I have used to achieve this is =COUNTIF(D6:AH6,"H").
I have transferred the figures in the 'Total' column across the other worksheets, however I now need to add any further holidays taken to the figure that has been carried over from the months previous.
I have added a couple of images. In January, the total number of days leave taken by Thompson is 6. This figure has been carried over onto the February worksheet as shown, however if I place more 'H's' on the same row then I need the total column to reflect both these and the ones from January.
Jan.JPG
Feb.JPG