=SUMPRODUCT(--(Jan!D9:D999 > 0)--(Jan!Y9:Y999="FALSE")) But it keeps returning the number of items in JanD9:D999 > 0 , when i want the number of items in column Y which contain "FALSE" if D is greater than 0.

It works I1 is blank and it works when F7:K7 has data and I1 is initialed. However, when I1 is initaled and there is no data in F7:K7 the cell reads "N/A". It should read "Approved with insufficient data".

=IF(ISBLANK($I$1),"",IF(ISBLANK(F7&G7&H7&I7&J7&K7),"Approved with insufficient data",LOOKUP(99999999999,F7:K7)))

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.

the formulas in sheet 2 of the attached file, the formula from rows 27 is returning nothing, and I think I’m not getting the formula right. It may need a little correction.

I am trying to average a data set where some rows at the moment are blank but will update at a later date, I am getting a div/0 error, the formula I am trying to use is

=IF(C23:C30="","",AVERAGE(C23:C30))

I thought this would look at the data and see if its blank leave it blank but if there are numbers there it would give me an average.

=VLOOKUP(G3,'Calculation for September 07'!C7:E1975,2,FALSE)

The formula is returning #N/A, but I see the lookup value in the table array. What do I need to add to the formula to have the lookup value recognized?

1) Not using a macro and combining relevant w/s functions, is there an intelligent way to return the value of: SUM(n=n1 to n=n2) of (-1)^(n-1)*(n)^(n-2)/(n-1)!*X^n to a cell containing the formula ??

2)Example: n1 in cell A5:: 1 (integer variable) n2 in cell A6:: 32 (integer variable) X value in cell A7::1.00 Returned sum in cell A8:: 0.567143 ...

I am using the OFFSET formula (=OFFSET($A$1,0,0,$B$1,1) and instructions (http://www.ozgrid.com/Excel/DynamicRanges.htm) I found on this site (which is great, by the way).

In the instructions on the site, it requires you to define a range for which the formula to apply. What I tried to do was skip naming the range, and simply using the OFFSET in various cells throughout the file. However, whenever I enter in a row [height] of greater than 1, the formula returns a #Value error. I want to use this offset on multiple data ranges, so naming all of them would be rather time consuming. Is there any way to skip this step?

I'm trying to write a SUMPRODUCT formula (cell H2 in the attachment) that gives different outputs according to the value of another cell (H1), but the output values are incorrect. I suppose it's because the "else" value given in the IF formula won't be recognized as a formula but as a text.

I'm currently using a series of SUMPRODUCT formulas to populate a summary page from a large data source. This works fine in the most part, however one part of the summary page calls for a number of different criteria to be counted within the same data range. I'm currently using a work around by having three seperate SUMPRODUCT formulas on hidden rows, and then totaling their answers to give me the result I want, however I wondered if there was a cleaner and better way to do this? An exampel of the formula I am currently working with is below:

I am trying to determine if I can write a formula that will read text in one cell, such as insurance and if true enter the amount from another cell and if false then 0. I have tried a IF statement by add the text to name manager. However, the answer is 0 rather than the amount in the selected cell. I might need to use a different formula or function.

I have two tabs (In-Transit) & (AC) column A in both sheet have a "Concatenate" that i copied and pasted and values only and also switch the format to "Text" on both tabs. I do a vlookup

[Code] ......

and it returns the results i want....But then i was thinking due to the volume of data what if the value in column A is found twice on tab "AC".. i wouldn't know with a "Vlookup".

So trying to build a Index/Match formula [Code] ....

I also returns some good results, but i also get some back as #REF. I also get #NA and both of the formulas and those are valid no matches...

The first two columns are an example of my data set; I would like a formula for the 3rd column (Length+1) which will give me the length at the next age (i.e the value in the cell below in most cases) unless the following age is 1 in which case I want it to return zero.

I know I can do this as I have already succeeded once but since moving a few things on my spread sheet the formula has gone wrong and I can't correct it.

I have one sheet with many different lines on and another I would like to select certain rows with the same reference number in column A. Below is the formula I am using

I have a reasonable grasp of Excel and this has me completely stumped. Three cells hold numeric values (format General though the format seems not to change the result)

C4 = 4082322.25 D4 = 4046689.05 E4 = 35633.2

Another cell contains the simplest of formulae ... F4 = "=C4-D4-E4"

Clearly the result should be zero. In fact the result is (to 15 decimals) ...

0.000000000189175

HOWEVER If I change F4 formula to "=C4-E4-D4" the result is zero.

This is probably a pretty basic question, but I can't find an answer in Excel help or on the interwebs. I have an IF formula and I'd like 0 returned if the statement is false (e.g. =IF(A1<>0, A1, 0)); however, I would like the 0 returned as text. I've tried the TEXT function and using ` in different places, but all to no avail.

All the answers I can find for this address either the auto-calculation setting or formatting the cell as a number. I am using this same formula in a bunch of cells to calculate totals: =SUM(E12:E17). It works find all over the sheet, except in one box. There is no difference in formatting or data type or anything.

Not working: notworkingexcel.png

Working: workexcel.png

The numbers being summed from the cells above the one in question are referenced from another sheet if that is relevant.

I am wondering if there is a way to return a cell reference by having excel look at a table with certain parameters. For instance... If there were a table with repeated dates such as: Feb 12 1500 1530 Feb 12 1745 1750 Feb 13 1215 1245 Feb 13 1610 1700 Feb 13 1850 1900 Feb 14 1050 1140 Is there a way to return the cell number (ie A6) for the first instance of Feb 14?

I am compiling data from several different, variable, worksheets. I am using concatenate to add the name of each worksheet into a formula to pull the appropriate data.

However, it is returning the text of the formula instead of the results of the formula. How can I make it calculate the result instead of simply displaying the formula text?

I am currently pulling in all cells from various worksheets, into a specific tabs in a workbook. Each source is pulled into a seperate tab such as Workbook A is pulled into tab Company A using the following formula which works fine.

Code: "Path[Workbook.xls]Worksheet!ReferenceCell"

Note: This is done due to the use of indirect in the next formula and its inability to work on closed workbooks. Within a summary tab, I am then pulling in specific fields from each of the aforementioned worksheets using the following formula

M217 - Worksheet Name M218 - Worksheet Date M220 - Worksheet Range M222 - Additional Fee B223 - Lookup Value

The issue is that one company out of 14 pulls into this workbook (using the first code) just fine with all values visible, but ONLY when the source file is open. Even if I manually update the link, the values do not change.

Is there a setting or something to check, maybe in the source workbook? I have ensured that automatic updates for links is on and there are no macros in the source workbook.

how to extract data like filtering but with a formula.

A B C D E

1 Cat1 Cat2 Cat3 Cat4 (Cum of Cat3) Return Cell

[code]....

I want to return data in Column E such that if Column A is Apple and Column B is Red, I want Column E to insert there "9", which is the last row matching this criteria (just once for Apples, Bananas etc.).

Notes: A Apples wont appear again in the coming rows.Reds can appear again in the coming rows.Column C is constant.Column d is the Cumulative.Normally what would you do manually is filter Apples and Reds and record the last value for Column D.