Using The Results Of One Formula In Another

Jan 9, 2007

what was the anser to a simple question earlier has now got me thinking...

I have one mega formula (see below) that based on a set of criteria returns a result from a range in an accompanying workbook:

=INDEX('input plus'!D940:D979,MATCH(1,('input plus'!$DJ$940:$DJ$979>=2)*('input plus'!$DG$940:$DG$979=1),0))

(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:

=CONCATENATE(ADDRESS(MATCH($A$3,'input plus'!$C$1:$C$3000,0)+0,4,4,1),":",ADDRESS(MATCH($A$3,'input plus'!$C$1:$C$3000,1)+0,4,4,1))

(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.

View 9 Replies


ADVERTISEMENT

Change Formula To The Results Of The Formula For Cells That Have Results

May 9, 2006

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.

example attached

View 9 Replies View Related

Results Show Results Of Formula, But Should Be Blank

Jul 27, 2006

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.

View 2 Replies View Related

Concatenate Formula Nested Within IF Formula Is Not Producing Expected Results

Aug 24, 2014

I am trying to use a combination of Concatenate and IF formula to produce an email.

My input.

D4: First Name:
D5: Middle Name:
D6: Last Name:

D8: Organization:

Once all these are filled, I want the formula to produce a result like

FirstName.M.LastName@Organization.com

I have used the following formula.

[Code] .....

Problem is if there is a middle name the formula works fine, but in case where there is no middle name, it produces the following result.

FirstName..LastName@Organization.com

How do I remove the additional (.) in cases where there is no middle name.

Attached File : Email Generator.xlsx‎

View 3 Replies View Related

Cells Display Formula, Not The Results Of The Formula

May 29, 2009

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," "))

View 5 Replies View Related

Use The Results Of A Formula As Column/row Numbers In Another Formula

Oct 1, 2009

I have two cells. The first cell has the formula:

=CONCATENATE("D",TEXT(MATCH($B$6,'Zip Ranges'!$D$1:$D$157,0)+1,"0"))

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.

View 4 Replies View Related

IF Formula (looks For Y And Results Y)

Jan 28, 2009

I would like a formula that looks for Y and results Y. See two examples below.

Example
Cell B2 = Y
Cell D2 = N
Cell E2 = Y
Cell F2 = Y

Example
Cell B3 = N
Cell D3 = N
Cell E3 = N
Cell F3 = N

View 4 Replies View Related

Formula Results

Dec 31, 2008

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 $ -.

View 9 Replies View Related

Copy Pairs From 2 Columns To Formula Reference Cells & Copy Updated Formula Results

Jun 24, 2008

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.

View 3 Replies View Related

IF Formula Returning Two Results?

Feb 21, 2014

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.

View 2 Replies View Related

Counting Results From A Formula

Dec 23, 2008

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.

View 3 Replies View Related

Conditional Formula Results

Dec 19, 2009

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?

View 3 Replies View Related

Formula Results In A Defined Name

May 7, 2009

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.

View 3 Replies View Related

Subtracting The Results Of A Formula

Oct 7, 2008

In A1 I have this: $9.50.
In A2 I want to display the result of multiplying A1 with 10% and subtracting the result from A1.

I've tried this in A2: =SUM(A1*10%)-SUM(A1)
But it displays a negative number: -$8.55

I need it to display: $8.55

View 3 Replies View Related

Formula Return The Same Results

Nov 11, 2009

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.

View 9 Replies View Related

Roundup Formula Results

Oct 26, 2006

Is there any way that I can apply the roundup feature to an entire workbook without having to do each cell individually?

View 9 Replies View Related

Copy Non Zero Formula Results

Sep 4, 2007

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.

View 9 Replies View Related

Chart Not Showing Results From Formula

Jun 16, 2014

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).

Read & Sign Tracking-061614.xlsx‎

View 2 Replies View Related

Vlookup And Match Results Given By A Formula?

Jan 22, 2014

I want to set up a formula which automatically does vlookup or same sort of function on numbers given out by a link through a formula.

As a example i have attached the sheet.

View 1 Replies View Related

Correct Formula Not Giving Results?

Feb 25, 2014

Cell A3 on my SHEET1 has the following formula =DATE(YEAR(C1),MONTH(C1) + 1,DAY(C1). It goes on like this for the next 12 months (+2, +3, +4....).

Column B on my SHEET2 has One of the five product codes (A, B, C, D, E) and Column D on my SHEET2 has manually entered dates

I created the following condition for each product code under each month in SHEET1

=IF(MONTH(Sheet2!$D$1:$D$39)=MONTH(Sheet1!$A$3),COUNTIF(SHEET2!$B$2:$B$39,"=A"),"")

The formula only works for two product codes (A and B), and then yields blank cells.

View 5 Replies View Related

Transfer Of Formula Results In One Cell To Another

Jan 25, 2009

how to take the result of a formula calculated in one cell and show the results in an other cell

View 4 Replies View Related

Vlookup Formula Is Not Displaying Any Results

Dec 18, 2009

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.

View 4 Replies View Related

Putting Text At The End Of Formula Results

Jan 8, 2010

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.

View 8 Replies View Related

VBA Formula Results In Concatenated Numbers

Apr 24, 2014

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.

View 3 Replies View Related

Ignoring Negative Results In A Formula

Jun 9, 2009

Ignoring Negative results in a formula
Question withdrawn

View 3 Replies View Related

Cell With Formula Does Not Display Results?

Aug 17, 2012

Just yesterday when I opened my excel file, all the cells that contains formulas does not display the results (leaving the cell blank).

For example the formula for cell A1 is =B5. But after putting a value in cell B5, cell A1 is still blank.

View 4 Replies View Related

Percent Change - Different Formula Results

Nov 21, 2012

In Excel, what is the reason for different results using the two formulas below?

Example:
Cell L6: 1413.5
Cell M6: 1312

Using the formulas below, the format of the cell is Percentage, one decimal place but I get different results

Formula 1: =-(1-L6/M6) The result is 7.7% change

Formula 2: =SUM(L6-M6)/L6 The result is 7.2% change

What is the difference in the formulas?

View 2 Replies View Related

VLOOKUP Using Results Of Formula As LOOKUP VALUE?

Oct 25, 2013

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.

View 9 Replies View Related

COUNTIF - No Results Return Only Formula

May 23, 2014

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.

View 9 Replies View Related

Creating Spaces In Formula Results

Jul 31, 2014

I am using a vlookup formula to link 3 pieces of data together. I would like to insert a space between the 3 pieces. Currently formula result is:

item1item2item3

I would like it to view as:

item1 item2 item3

not sure how to alter the formula to create the space....

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved