(the range 940:979 is a filled with investment funds data all within a particular sector, this formula for example will return the name of the number 1 rated fund, ie "mega star cash")
However, the above assumes that the ranges specified wont change, but they do, frequently and there are over 300 of them, so I earlier got help to create the following formula which can tell me what range a certain set of data lies within:
(so I tell this formula to look at a specified sector name 'cash funds' and by tweaking the number in bold it will return the range of data that i want to feed into the first formula, ie it will return D940:D979, DJ940:DJ979 and DG940:DG979)
If the data ranges change then these cells will dynamicaly update, either the range will shrink or grow - which is perfect - what i want to know is if i can integrate this into the first formula, so if you like it becomes completely dynamic.
I have a range that has formulas that are based on other fcells outside the range.
What I want to do is, if the cell has a value to remove the formula and paste the results. If the filed is blank (no results from the formula) to leave the formula in place.
There could be a marco to run when this process is needed.
I am trying to create formula that will show overtime worked in a given day. The code I am using is a simple one ([ cell - 8], for hours worked). The problem is when the time cells are blank/not used it shows a -8 in the cell. what I need to do to create a code that will eliminate the -8 from showing. The cell its self is taking the result from another cell with a formula and then subtracting 8 from the result of the formula in the other cell.
To get cell to display results of formula I must edit (F2) and hit enter. Is there an easy way to do this in bulk rather than editing and hitting enter on hundreds of cells?
Example of formula: =IF(COUNTBLANK(K2)=1,"",CONCATENATE("*** ",K1,": ",K2," "))
which results in a col and row number (such as D65).
The second cell has the following formula:
=INDEX('Zip Ranges'!$A:$B,MATCH($B$6,'Zip Ranges'!D1:$D$157,0),2) ^^ I wish to replace the 'D1" in the Match function with the results of the first cell's formula.
I assume Indirect would work, but I don't know how to code the formula to use it.
I have created a simple SUM formula to add cell values across six sheets in the same workbook. I see the result when I use the formula button but the result (number) does not appear in the cell. All I get is $ -.
I am currently working on a data analysis project (data mining) and need to collect and later analyze statistics for the inputs which control a series of calculations. These statistics are shown in the Statistics 1, Statistics 2 and Statistics 3 cells in the workbook that I attached. The inputs are X,Y; all possible values for these inputs are listed in the N,O columns. Basically I need a macro which would take the values from these two columns and place them pair after pair into the controlling cells (K3, L3), then it would copy cells H2 through L3 (updated stats) to a new sheet after each copy operation - so that I will finally have a list of statistics for all of the input pairs.
I am using this formula. How can I get it to pull 'Sales Materials'!$F17:$F69' and display along with 'Sales Materials'!$B$17:$B$69 when my logical statement is true.
=INDEX('Sales Materials'!$B$17:$F$69,SMALL(IF('Sales Materials'!$E$17:$E$69="YES",ROW('Sales Materials'!$B$17:$B$69)-16),ROW(A1)),1) I've tried using * but I think something is going on with "-16" which I was told to you use to account for headers.
I have a spreadsheet which matches Reference numbers in Column A against Column B and vice versa; and assigns unique or duplicate values. I would like to be able to count the number of duplicates and uniques giving a total of how many appear in the spreadsheet. I've attached the spreadsheet to give you an idea of what its like.
Hello, I cant figure out how to do this! I have a cell B2 that I enter a number in and in cell C2 I want it to add the number from B2 and add 15 to it. I can get the formula "=B2+15" and it works but I wont always have a number in B2 and I want it to say "0" and "0" would be in C2. Any ideas?
Is I have a VLOOKUP formula which results in a defined name- great! but when I reference that result in another formula I get a #REF error, if I type the defined name directly in to the formula (index btw) all works but I need the formula to recognise the referenced cell.
Example
=VLOOKUP(B8,Cable.Table,2) this results in a defined name of a table (D1A) in cell B30
The cell B30 is named "table". I then have another formula in B33 which is:
=index(B30,B47,C12) or =index(table,B47,C12) Which gives me the #REF error
If I input =index(D1A,B47,C12) the function works but it needs to be the initial reference from the result of the other formula and recognise that it is a defined name.
The formula in Col G uses the value in col's B:E. I would like a formula in Col G that will use the 4-digit value in Col F ( same values as Col B:E) and return the same results.
******** ******************** ************************************************************************>Microsoft Excel - PLAY 4 EVE MOSTLY.xlsx___Running: 12.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutF1G1F2G2F3G3F4G4F5G5F6G6=ABCDEFG111/10/0966666666Q211/09/0977667766DD311/10/0977767776T411/11/0972467246S511/12/0977667766DD611/13/0978667866DSheet1 [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
I have 4 source workbooks and a master workbook which is linked to the source workbooks, if the source workbooks have no value in specfic cells the master workbook which is linked to it shows a 0 (not a problem). Now if I want to copy only cells which have a value other than the 0 to the end of used rows on another sheet on the Master workbook using VB I run into a problem because it sees 0 as a value and copies as well.
The issue is how do I stop the 0 showing and not being seen as a value. This 0 shows regardless of format.
I have a spreadsheet (attached) that has a formula, which calculates the number of days between two dates if another field contains data. My problem is that when I chart the data, it isn't showing up in the chart. Cell G1 (Tab 1) contains a 1 because the document was returned 1 day late. This isn't showing up on the Chart (Tab 2).
i put in a vlookup formula and it doesn't display any results, it just diplays the formula with the = sign in the cell. it does that with any formula i enter. i tried hitting it and everything.
I'm trying to enter relatively simple drug calculation formulas in Excel where the result is appended with the proper units to administer. For example, if I'm calculating for units of regular insulin with a formula such as:
=0.75*f2
I'd like the cell to make the above calculation, and append it with "Units Reg Insulin" or something similar. Thus far, I've just been putting the units as text in adjacent cells but the result looks a bit messy.
Is this possible? I'm using Excel 2007 and the Help index does not seem to offer much guidance about this.
I need to show sum and not concatenated values have a userform w 60 fields for numbers as follows:
a) UserForm1.TextBox34 to UserForm1.TextBox41: 8 fields-CAD currency b) UserForm1.TextBox42 to UserForm1.TextBox49: 8 fields-USD currency c) UserForm1.TextBox50 to UserForm1.TextBox57: 8 fields-exchange rate d) UserForm1.TextBox58 to UserForm1.TextBox65: 8 fields-Total CAD currency => the formula here works across the board UserForm1.TextBox58.value = Format(UserForm1.TextBox34.value+UserForm1.TextBox42.value*UserForm1.TextBox50.value, "###,##0.00") e) UserForm1.TextBox66 to UserForm1.TextBox73: 8 fields-local taxes in CAD currency f) UserForm1.TextBox74 to UserForm1.TextBox81: 8 fields-federal taxes in CAD currency g) UserForm1.TextBox82 to UserForm1.TextBox89: 8 fields-total for first column in CAD => the formulas here works as well across the board UserForm1.TextBox82.value = Format(UserForm1.TextBox34.value + UserForm1.TextBox42.value * UserForm1.TextBox50.value + UserForm1.TextBox66.value + UserForm1.TextBox74.value, "###,##0.00")
have a grand total formula pulling in all values, and this one works as well; so i have 4 simple addition formulas, and the local & fed tax formulas do not.
however, the formulas for the sub-total for the local taxes and the federal taxes do not work; the results show a concatenation of the 8 values rather thn the sum of the 8 values; the formula for the local tax is as follows: UserForm1.TextBox110.Value = Format(UserForm1.TextBox66.Value + UserForm1.TextBox67.Value + UserForm1.TextBox68.Value + UserForm1.TextBox69.Value + UserForm1.TextBox70.Value + UserForm1.TextBox71.Value + UserForm1.TextBox72.Value + UserForm1.TextBox73.Value, "0.00")
I have rewritten the formulas and still these two tax formulas result in concatenated numbers rather than the sum.
I would have u/l worksheet but it's roughly 4MB now.
I have a cell that I must remove the first 2 characters "RO" for each value in a column on a sheet called RAW DATA and put into a cell on a sheet called ROSS DATA. Some of the values in that cell have 3 digits after the RO and some have 5 digits. To do that I used
=REPLACE('RAW DATA'!A3,1,2,"")
Then I need to use this new resultant string as the lookup value in a VLOOKUP. The VLOOKUP will be looking at a named range called DAP on a sheet called DAP, in column 5 for an exact match and I need it to return that value to the cell.
I have tried using the indirect to no avail in different ways, and not sure that I fully understand the usage.
I am trying to track the number of "F"s within a cell and I'm sure that I'm using the formula correctly -
=COUNTIF(I2:I29,"F").
When I use the insert function feature to walk through setting up the function step-by-step - I see the count once I type in the criteria - however, when I hit 'Ok' the count doesn't appear in the cell - the formula appears. I have cut and pasted the entire worksheet into a new spreadsheet, the problem remains.
