## New answers tagged worksheet-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 ...

-1

Sorry to be so very brief, but the answer is not that complicated really:
You need only change CONTENTS(...) into INDIRECT(...) and it should work nicely.

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

0

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

0

You will need to do something like this to go across sheets-
=LARGE(CHOOSE({1,2},Sheet1!B1:B4,Sheet2!B1:B4),1)

0

There are many methods to do that, but I'll go with the simple and quick methods to do it.
Method 1:
You can use Excel formula to do to calculate inside the cells, you just need to click on a cell and type = then do the calculation and press Enter.
Example : =1+1
(This will show the sum of 1+1)
The problem with this method, is that you'll have to ...

2

There IS a simple way of doing this.
Say you have 50 in the cell in question, and want to add 30 to it.
Type 30 in a random cell somewhere else.
Copy (Ctrl-C) that cell
Then the clever part:
Right-click on the cell with 50 in it, and select Paste Special.
In the box that comes up, select 'Add'.
This will add the value directly, without you having to do ...

-1

=IF(AVERAGE(lookup(2,1/C44:C46<>"")=1,2,SUM(LOOKUP(2,1/D44:D46<>""),0)))
IF(AVERAGE(lookup(2,1/C44:C46<>"")=1,SUM(LOOKUP(2,1/D44:D46<>""),0)))
last row entry match example 1 or 2 so please correct formula

2

=SUM(COUNTIF(K:K,{"Busy","Hang up","Stopped","Voicemail","Wrong Number","No Answer","IP Phone Offline","Call accepted"}))
If this list is subject to change/expansion then it would make more sense to store the criteria in cells within the worksheet, rather than within the formula itself. For example, if the criteria were in A1:A8, the required formula would ...

1

You can use the following to make it look good.
1) Go to Formulas tab - under Defined names group - select Name manager (Or for old versions of excel using menus - Insert>Name>Define) and in the "Names" box type: calldetails.
2) Scope - can be workbook.
3) Now click in the "Refers to" box and type (or paste): =COUNTIF(K:K,"Busy")+COUNTIF(K:K,"Hang ...

0

It sounds like you could use a VLOOKUP to look up the value of the day that is entered. Your range would be day 1 to day x and the calculated value for each day.
It would be helpful to see your workbook formulas for what you are trying to accomplish. Try using Ctrl+` (the button next to the 1 key on your keyboard) to show all formulas and then taking a ...

2

Sounds like a job for pivot table!
Select the range and go to Insert - Pivot Table
For row labels, have whatever your rows are.
In the values field place your numbers, click the drop down and go to value field settings.
Now show value as and select % of total in the drop-down.
Now they are summarized by percentage of total!
click for full size image

1

Just thought I'd share the solution I eventually went with since it does the job.
Couldn't make a user defined function that does this, doesn't seem to be possible so I went with a basic macro.
Sub Formulas()
Dim rng As Range, cell As Range, y As Integer, o As Integer
y = Range("B1").Value
o = (y - 3) * 2
Set rng = Range(Cells(3, 4), Cells(3, y))
...

0

George, if I understand correctly, you'd like the output to be the codes separated by commas...
Have you tried using VLOOKUP to look up the individual codes based on the text (eg manufacturer name) and then concatenating the looks ups with " ," if you need to have everything in the same cell?
I visualize something like...
=VLOOKUP(manufacturerTextCell, ...

0

Before pasting, select column that will hold the non-date value. Right click. Select 'format cells'. Then select 'text' as the format and click OK.
Now, go ahead and paste, but paste using the 'match the destination formatting' option.

2

Using INDEX works too.
=SUM(INDEX(A:A,B1):INDEX(A:A,B2))
Referencing an earlier answer which suggests using this method instead of INDIRECT.

1

Yes, use the INDIRECT function
=SUM(INDIRECT("A"&B1&":A"&B2))
Note: I haven't tested this formula, let me know if there are any problems

0

As has been stated in the comments, formulas (worksheet functions) can’t modify cells.
But if you’re content with displaying the values you want, this is easy.
Assuming that I understand what you want.
If your data are in Column A, starting in Row 1, then enter
=IF(NOT(ISBLANK(A1)), A1, 1)
into cell B1, and
=IF(NOT(ISBLANK(A2)), A2, ...

0

I think the easiest thing to do is to break up the IP into octets first. Then you could use concatenate to rejoin octets 1 & 2. From there you can use conditional formatting, vlookup or whatever method you like to compare the values.
I was looking for some guidance on manipulating IPs for a similar pet project and found the following article to be ...

3

Sure, you can use this formula and drag it down -
=INDIRECT(CONCATENATE("Sheet",ROW(),"!$A$1"))
It will use the row where it's at to determine the sheet number.
You could also use something like this
=INDIRECT("Sheet"&ROW()&"!$A$1")
these both use the INDIRECT() function, which is volatile.

0

You can apply the format manually using the TEXT() function:
="('"&A6&"','"&TEXT(B6,"dd/mm/yyyy")&"','"&C6&"','"&D6&"','"&E6&"'),"

0

The absolute easiest way to do this is to sort the data in descending order of sales and do a simple vlookup based on store name to return the date. It'll return the correct date because vlookup returns the first instance of the TRUE value.
If that's not possible then...
The following array formula gets the job done (enter this formula by pressing Ctrl + ...

0

Add the new accounts to the bottom and use the excel sort functionality to re-sort the data by whichever criteria required. Using a pivot table table sounds like a great alternative; contingent upon whether your data is week-to-date, MTD, or YTD.

0

Assuming your 'Last Month' worksheet has the number of infections in cell B2, you could do the following from any other worksheet in the workbook:
=('Last Month'!B2)
That tells Excel to look in the worksheet called "Last Month", and retrieve the value from cell B2. You can also use this in formulae like =SUM(C77+'Last Month'!B2), which will add the ...

2

If your temperatures are in column A, you can put this in cell B1 and drag it down
=(A1-MIN(A:A))/(MAX(A:A)-MIN(A:A))
Change the format of those cells to percentage and baby, you got stew going!

0

Use an if statement =if(A1="February",B1,B1*30/day(today()). If it is February use the value in B1 if not Forecast it forward for a 30 day month by multiplying 30 divided by the current day of the month

1

This is what Pivot Tables are for in Excel.
My answer is for Excel 2013:
Select Insert Tab
Select Recommended PivotTables to let Excel guess what data you want to see
You will see the first example is exactly what you want:

0

You could add a condition to exclude blank cells. For instance:
=SUMPRODUCT(LEN(X2:Z2)-LEN(SUBSTITUTE(X2:Z2,",",""))+1,--(X2:Z2<>""))

0

You can use SUMIFS! Here is an example illustrating what you described below:
It is not 100% to where you want it but try using the formula that I illustrated. Modify it and learn how to manipulate it.

1

The difficulty is that there are many possible ways the sampling can go because of the unknown ratios of things like the file count vs. number of unique codes vs. codes with a single file vs. files > 150 days vs. distribution of the number of files per code, etc. Rather than trying to design a definitive solution for all possible cases, I'll describe a ...

Top 50 recent answers are included