## New answers tagged worksheet-function

0

This would be a lot easier if you would add a column to identify funding requests.
It can be out of sight; e.g., column Z. Set Z1 to 1, and set Z2 to
=IF(B1=B2, Z1, Z1+1)
and drag that down to the bottom of your data. Then the denominator you want is
COUNTIF(Z$1:Z$9999, Z1)
(adjusting the 9999 to cover all your data), and so C1 can be
=B1 / ...

0

=SUMPRODUCT(COUNTIF($A$1:$E$1,$G$1:$G$50))>=1
will return TRUE or FALSE for your conditions

0

I ended up doing this. Thank you all for your comments, the answer from fixer1234 was specially useful. The LOOKUP wasn't giving accurate results, so I changed it for a INDEX/MATCH pair. Also added a N/A check.
IF(ISNA(INDEX(INDIRECT("Runs!A3:A"&COUNTA(Runs!A:A)), MATCH(A3, INDIRECT("Runs!D3:D"&COUNTA(Runs!D:D)), 0))), "", ...

0

The presence of the "" placeholder, which forces the cell to be evaluated and appear as blank, means that the cell temporarily assumes the (text) format of the placeholder. You can't put something in a cell to act as "empty" and not have something there (you can't eat your cake and have it, too). You would need something like VBA to actually delete all ...

0

Sure, you can use a match function. In A2 put -
MATCH(A1,$G$1:$G$50,0)
Drag this across row 2, and it will return a value (the row number) if it's found in both lists. If it's not found, it will give you #N/A
Then you could, for instance, put in a formula like =ISNA(A1:E1) as an array formula (Ctrl Shft Entr) and FALSE would indicate if any were found, ...

1

It is hard to tell without seeing the actual data, but what I'd reccommend to check is if the format of the given cells where the IDs are stored are indentical in the two sheets.

0

If you have a text value like 8:00 in, for example, cell J42, then put
=TIMEVALUE(J42)
into cell K42 and you will get an Excel date/time value of eight hours.
It will display as 0.33333333 by default (because 8 hours is ⅓ of a day),
but you can format it to display as a time value.
And, depending on your needs, you may want to copy column K and paste ...

0

Follow the steps as following:
Collate the data (i.e. sum the amounts) for a data and description (merchant) in both sheets. You do not need to do this is there is only one row for a combination of date and description.
Create the unique key for each row in new column by joining Date and description (you can use excel concatenate function), in both sheets.
...

0

I discovered that INDEX(PPRICE, 2) will return the value of the second row in the PPRICE column. The entire code snippet will look like this:
=IF(INDEX(PPRICE, 2)="",
INDEX(DPRICE, MATCH(Products!A2, DCAT, 0)),
INDEX(PPRICE, 2))
However it will be an absolute value, not a relative value and if you copy paste this formula to other rows it will still point ...

0

You can do this using an array formula. Say The 50 columns of data are in columns A through AX, row 2 is your first data row, and the formula is going into AZ2:
=50-MAX(IF(NOT(ISBLANK(A2:AX2)),COLUMN(A2:AX2),0))
The formula needs to be entered using Ctrl-Shift-Enter so it goes in as an array formula. Once it is entered, you can copy and paste 49 ...

1

There are two types of "lookup" functions. One will find the location of something, the other will return the contents of that location. A function like VLOOKUP will return the contents. A function like MATCH will return a location.
If you are looking specifically to mirror the results (find the location of the cell that VLOOKUP returns), you can do ...

1

VLOOKUP() is made for tasks like this. It will take a rectangular range look up a given value in the first column, and return the value in the same row from a different column.
In your example, if the first visible cell ("Current Month") were cell A1, then I'd write the formula like this:
=VLOOKUP(A2, B2:D6, 3, TRUE)
A2 is your reference value, B2:D6 is ...

0

You can use the VLOOKUP() function for finding the rental increase value based on the row range match, something like this:
=vlookup(A2, B2:D6, 3, TRUE)
assuming the cells in the picture are placed at A1.
This should return the rent increase in the cell where you put the formula.

0

Assuming you have data in rows 2 to 10 you can use this "array formula"
=IFERROR(INDEX(B$2:B$10,MATCH(1,COUNTIF(C1,A$2:A$10&"*")*(A$2:A$10<>""),0)),"No match")
confirmed with CTRL+SHIFT+ENTER

0

You can use this formula
=SUMPRODUCT(SUBTOTAL(3,OFFSET(H3,ROW(H3:H5)-ROW(H3),0)),(H3:H5="void")+0)
That will give you a count of visible cells in H3:H5 (after filtering) that are "Void".

0

It isn't clear what role C1 plays. If the sought strings already exist in a column, you can use that as what you show as Col A of Sheet2. If they are somewhere else you can build Col A by just referencing the associated cell in the other list. For example, if the list is in Col Z, starting in row 2, A2 would be =Z2, and you could copy that formula down ...

1

I get it--the problem is getting your dynamic range to work as a range specification within LOOKUP. You were almost there. Try this:
LOOKUP(A3;INDIRECT("Runs!D3:D"&COUNTA(Runs!D:D));Runs!A3:A6)

1

the offset() function will solve your issue
replace Runs!A3:A6 with OFFSET($A$3,0,0,COUNTA($A$3:$A$1000),1)
you can get a bit fancy with a Dynamic Define Range http://support.microsoft.com/kb/830287

1

Say you want to find those in col. B with no match in col. A. Put in C2:
=COUNTIF($A$2:$A$26;B2)
This will give you 1 (or more) if there's a match, 0 otherwise.
You can also sort both columns individually, then select both, Goto Special, select Row Differences. But that will stop working after the first new item, and you will have to insert a cell then ...

2

Here's one solution. It assumes the data starts in row 1 (if not, offset the row references). The formula in A1:
=IF(C1="TOTAL",COUNTIF(C$1:C1,"TOTAL"),ROW(A1)-COUNTIF(C$1:C1,"TOTAL"))
Copy that down column A for the whole data range.

0

I get what you're trying to do: for a range of values in column A and a range of dates in column E, how many values in column A exist with a corresponding date in column E less than date. I'm not sure how you're trying to report those - in a matrix like this?
| G | H | I | J | K ...
1 | | 11/01/2014| 12/01/2014| 01/01/2015| ...

0

No, but set it up this way, and Tab and Shift+Tab will take you to the next and previous highlighted row. First do this:
Change your formula to =IF(MID($C2,5,1)=" ", 1, "") (copying down, of course)
Highlight the column (Ctrl+Space will do that if you're in any cell in that column).
Press F5, then Alt+Special, Formula, de-select Text, then Enter
To ...

1

You could replace all your blank cells with something like zzzzz. Be sure to choose the option Match Entire cell contents if your data has blanks. Then select your range to copy, paste it were you need it. Then change all the zzzzz to a blank on the new source.

0

I agree the date and sample ID should be stored in a seperate column but if you just need to extract the month you could use the below
Cell Value Sample B 1/1/2014 16:43 (assume this is mm/dd/yyyy)
=MID(A1,FIND("/",A1)-2,2)*1
Result is 1.
How this works.
This finds the first / in the cell and returns the 2 digits in front of that.
The * 1 converts the ...

2

Just take your result one step farther. Say the number is entered in B1. In C1:
=IF(ROW(c1)>$B$1,"",INDEX($A$1:$A$148,RANDBETWEEN(1,148)))
Copy this down the column. Now you will get a random selection for the number of rows in C specified in B1.

0

Just some suggestions: if you can, separate the sample name and its datatiem into two columns (or three columns: sample, date, time), then use the sort/filter function provided by Excel.
Another idea is add new columns, and write a simple function to extract the date string from the cell containing sampling time, then sort/filter by the column(s) containing ...

0

In language, you want (as I understand it): IF A11 is equal to "1B 22 1 5", THEN show the value of B10 ELSE add 1 to B10.
In Excel, this can be expressed as the following formula: =IF( A11 = "1B 22 1 5"; B10; B10+1 )
See Excel's help on the IF function.

0

I'll close the loop and summarize the comments in an answer. There appears to be no way to expand or replace a cell reference with the cell contents inside the CHOOSE function. However, @Excellll suggested an equivalent solution: the SPLIT function, which exists in VBA but is not accessible from the worksheet. This would be used with INDEX rather than ...

0

You could use the following VBA code to change an imputted value that countains "SIETCO", to "EBTBSIET" over the range A1:A10. You should be able to easily adapt this to your need.
In the code editor, you need to add this code to the sheet you wish to use it on, rather than a module.
Private Sub Worksheet_Change(ByVal Target As Range)
If ...

1

Why not break up the data. Select column B and go to data tab - text to columns - delimited - comma
Now in B3 put =Concatenate(B1," ",B2) and drag it across.
Now they are paired. You want them back together, use concatenate again.
macro
Sub test()
Dim arrdate As Variant
Dim arrtime As Variant
Dim strcombo As String
arrdate = Split(Range("B1"), ",")
...

2

=AVERAGE(OFFSET($A$1,(ROW(A1) - 1) * 5,0,5,1))
I put this formula in B1, copied and pasted down column B.
It is performing an average on a range found using the offset formula.
OFFSET (REFERENCE, ROW OFFSET, COLUMN OFFSET, HEIGHT, WIDTH)
REFERENCE = Absolute reference to A1
ROW OFFSET = Current row number minus 1 times 5
COLUMN OFFSET = 0
HEIGHT = 5
...

0

Select the column containing the values and run this small macro:
Sub YYEESS()
Dim rng As Range, r As Range
Set rng = Selection.Cells.SpecialCells(xlCellTypeConstants)
For Each r In rng
If r.Text = "TRUE" And r.Offset(1, 0).Value = "" Then
r.Offset(1, 0).Value = "YES"
End If
Next r
End Sub

0

The approach below might not be a clean one, but it still would work!
Follow the steps below to populate YES below every TRUE row:
Select the data and enable Filter over it.
Now, unselect "TRUE" from the column of interest, so that you will be displaying only the "Blank" rows in that column
Enter the following formula in each of the blank cells in that ...

1

Here is one solution. Assume the first data row is row 2. I stuck 100 in as the last data row; make that whatever it is (or you can get fancy with functions and calculate it). Formula for G2:
=IF(AND(C2=4,OFFSET(D2,MATCH(6,C3:C$100,0),0,1)=D2),"Y"," ")
Copy that down for all data rows. Note that the last entry will be #NA. If it matters that it ...

0

With some help from Scott Harris answer I created a button on the first sheet called "Update Headers" and then used picked the cells that i wanted to create text from the named cells, and then looped through all of the worksheets and updated them as well.
Sub Update_header()
Dim WS As Worksheet
Dim HeaderText As String
HeaderText = ...

0

Create a new column on the old version. The cell in each row will contain a lookup of the ID in that row against the ID list in the new version and create a flag if it is not a duplicate. Say the ID is in Col A; for row 2:
= isna(match(A2,new_version_sheet_ID_range,0))
Make the range reference to the new version absolute (dollar signs). Copy the ...

0

I would use the Power Query Add-In for this. It has an Append command that will combine the rows from two separate sources into a single table:
http://office.microsoft.com/en-au/excel-help/append-queries-HA104149760.aspx
Power Query has many other transformation functions to massage your data along the way.

0

Google tells me that this has to be done with VBA:
http://excel.tips.net/T002522_Putting_Cell_Contents_in_Footers.html

0

An alternative approach is to use COUNTIF:
=COUNTIF($A$1:$A$1000,B2)>0

1

You can use MATCH to do most of the work. For Mess in Column A and Check in B, you can use the following to generate your TRUE/FALSE match values.
=NOT(ISERROR(MATCH(B2,$A$2:$A$1000,0)))
This returns TRUE if the Check value is found in Mess and FALSE if it's not found.

0

The offset function may take care of this logic ... [Current Column]:[Current Column - 11] ... but it can't be nested with the sumif, so I think DSUM may provide the easiest solution.
http://office.microsoft.com/en-us/excel-help/dsum-HP005209069.aspx

0

The simple answer to what you described is this. The month to month change could be found by just inserting a row (row 3 in your example), and the value in each column would be the cell above minus the cell to its left. For example, G3:
=G2-F2
If you want that as a percent:
=(G2-F2)/F2.
Format the result as a percent. If I'm missing what ...

0

The following formula will provide you the increase/decrease of the last month input vs the previous one.
=OFFSET(E2,0,COUNT(F2:Q2),1,1)/OFFSET(E2,0,COUNT(F2:Q2)-1,1,1)-1
This following formula will provide you the % variance of the last month compared to the rolling average.
=OFFSET(E3,0,COUNT(F3:Q3),1,1)/AVERAGE(OFFSET(E3,0,1,1,COUNT(F3:Q3)-1))-1
If ...

0

vlookup seems like a great option to look up the information you need especially if you have a unique identifier.
If the data types are different then you can try using the value() function to convert text to a numerical value or text() function to do the reverse. If there are extra spaces of either side then trim() might do the trick.
The easiest way to get ...

0

From 'Page Layout' in the ribbon, select 'Print Titles', then select the 'Header/Footer' tab.
This gives the custom features that you want.
You can insert fields from the Document Properties here if you wish.

0

I'd use the SUMIFS and COUNTIFS formulas. For example:
=COUNTIFS(Data!B:B, "Admission", Data!C:C, "HospA", Data!D:D, "<>"&0)
The SUMIFS formula works slightly differently; you need to specify the column you are summing first and then start referencing your criteria columns and their corresponding criteria
=SUMIFS(Data!A:A, Data!B:B, ...

1

You are correct, vlookup is the answer - =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
So for you, in the childlist_value you would put (in D1)
=Vlookup(C1,!$A$1:$B$100,2,FALSE)
Or across sheets, in child_list value (Sheet2!B1) -
=Vlookup(A1,Sheet1!$A$1:$B$100,2,FALSE)
This will take the value on child_list (A1), match it to ...

0

If you delete the curly brackets in your example, the CONCATENATE function will work as desired. I did find one website that tips us to use the formula, for example, =TRANSPOSE(A1:A50). Before pressing Enter, press F9 instead. It will fill the contents with the result in curly braces exactly how you have it. Then delete the braces and utilize the ...

1

I haven’t actually tried this with 10,000 rows, but try
=INDEX($S$1:$S$50, MATCH(MAX(COUNTIF($G$1:$G$10000,$S$1:$S$50)), COUNTIF($G$1:$G$10000,$S$1:$S$50), 0))
where the unique state values are in S1:S50. (Naturally this is an array formula;
so you need to type Ctrl+Shift+Enter when you enter it.)
COUNTIF($G$1:$G$10000,$S$1:$S$50) counts how many times ...

2

Your formula is failing because you're referencing an entire column rather than a specific range. This will, naturally, find the most common value to be blank..
You can fix this by using this (extremely volatile) function -
=INDEX(INDIRECT("G1:G"&COUNTA(G:G)),MODE(MATCH(INDIRECT("G1:G"&COUNTA(G:G)),INDIRECT("G1:G"&COUNTA(G:G)),0)))
I don't ...

Top 50 recent answers are included