## New answers tagged worksheet-function

0

The following formula neither needs additional cells nor does it have to be entered as an array formula:
=RIGHT(A1,LEN(A1)-FIND(LEFT(SUBSTITUTE(A1&" ","0",""),1),A1&" ")+1)
If a single zero shall be returned for strings like 0 or 00, the following formula may be used:
=IF(A1="","",RIGHT(A1,LEN(A1)-FIND(LEFT(SUBSTITUTE(LEFT(A1,LEN(A1)-1)&" ...

1

Ok, I think I got what you're trying to do and I think VLOOKUP could not be the way to go for this, using MATCH instead. Let's begin...
1ST APPROACH "all-values-in-a-single-cell":
On sheet "tickets"
C2 cell put formula =IFERROR(1+MATCH($A2,'tickets_info'!$A$2:$A$5000,0),0);
D2 cell put formula =IF($C2>0,INDIRECT("'tickets_info'!O"&$C2),"");
...

0

The problem you're having is because when xcel saves a sheet as CSV, it actually separates the values with a semicolon (;) and not a comma.
So once you've saved the sheet as a CSV, close the sheet and then open the sheet with notepad. Here you will see the values separated with ; - Now click Edit, Replace, and in the "Find" field type the semicolon (;) and ...

0

How about INDEX and MATCH?
Tutorial:
http://www.mrexcel.com/articles/excel-vlookup-index-match.php
You can do a MATCH in one column and use that in the INDEX function for the other columns in the same row. That way you only lookup the row once.
You would basically have two tables on top of each other. The top one showing values aligned with table 1 that ...

2

Using the mouse does not mean that you have to drag a formula down hundreds of pages. You can use a simple double click instead.
Enter the formula into C1, then double-click the Fill Handle (the black square at the lower right corner of the cell)
Before the double click:
After the double click on the fill handle:
Edit: How far will Excel fill down? ...

1

You can use Ctrl and the arrow keys, with or without Shift held down, to navigate around the spreadsheet quickly. Ctrl+arrow keys jumps directly to the "last" cell in that direction which has content. Holding down Shift selects cells as you move around. Depending on how your spreadsheet is laid out, you may also be able to use Ctrl+Home (jumps to the top ...

0

Much as been already suggested, you need to check if you are returning a result. As you only want to check existance, a VLOOKUP function is a bit of overkill. I would suggest a simpler MATCH function with a check to see if it is returning a row number (found) or error (not found).
=IF(ISNUMBER(MATCH(D4, '[05.14_Wave 5 Priority 14 (Big+Cranberry) ...

1

Assuming the sheet wherein you stored your list of codes is called CodeSheet
={SUM(IF(IFERROR(MATCH(f3:t3,CodeSheet!$A$8:$A$22,0),0)>0,f4:t4))}
This is an array formula. Enter it without the curly braces, and hit control-shift-enter. Excel will put the curly braces in for you.
How it works:
MATCH(f3:t3,CodeSheet!$A$8:$A$22,0)
...

0

Wrap each VLookup in NOT(ISERROR()) to achieve what you're looking for:
=IF(NOT(ISERROR(VLOOKUP(A1,sheetname!A:A,1,FALSE))),"some statement","another vlookup")

0

You can do this with a formula.
Do an index/match lookup through each sheet. If the sheet does not contain the number it is cached by the IFERROR and you try to look in the next sheet.
Below formula looks through all 5 worksheets :) Paste it into cell B2 in sheet ATT and copy it down...
=IFERROR(
IFERROR(
IFERROR(
...

-2

To switch Excel columns and rows,
1.Open the spreadsheet you need to change.
2.If needed, insert a blank worksheet.
3.Click the first cell of your data range such as A1.
4.Shift-click the last cell of the range. Your selection should highlight.
5.From the Home tab menu, select Copy.
6.At the bottom of the page, click the tab for the blank worksheet such as ...

2

If we have this in Sheet1:
and run this short macro:
Sub ReOrganize()
Dim sh1 As Worksheet, sh2 As Worksheet
Set sh1 = Sheets("Sheet1")
Set sh2 = Sheets("Sheet2")
Dim N As Long, M As Long, i As Long, j As Long, K As Long
Dim t1 As String, t2 As String
N = sh1.Cells(Rows.Count, "A").End(xlUp).Row
K = 1
For i = 1 To N
...

1

The change to incorporate multiple columns is quite simple. Only one line of code needs to be changed:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim A As Range, B As Range, Inte As Range, r As Range
Set A = Range("I:I,K:K,P:P")
Set Inte = Intersect(A, Target)
If Inte Is Nothing Then Exit Sub
Application.EnableEvents = False
For Each r In ...

2

A function is part of a formula. So the below should work in exactly the same way.
Select the column you want:
Paste your formula:
Then hit Ctrl + Enter.
Done!

0

What you are describing could be accomplished with a pair of offset() functions:
B2 =offset($a$1, row()*5,0)-offset($a$1, (row()-1)*5,0)
offset expects a starting cell, the number of rows to offset by and the number of columns to offset by.
row() returns the current row number
so =offset($a$1, row()*5,0) references A5 in the first row (row()*5 = 1*5 =5)
...

1

If your sheetname includes spaces, you must use single quotes:
='My Sheet'!B3

1

How do I reference an excel sheet name in an excel formula?
Use the following syntax:
SheetName!CellAddress
Notes:
The worksheet name comes before the cell address, followed by an exclamation mark !.
If the worksheet name includes spaces, enclose it in single quotation marks '.
Example:
'Sheet Name with spaces'!CellAddress
Further reading
...

1

You simply use
=Sheet2!C25
In this case, I'm referencing Sheet2, cell C25
If I had named the sheet something like DataSheet, I would use
=DataSheet!A1
The above would access the A1 cell of DataSheet
So, if my DataSheet A1 had the value 5, and my Sheet1 A1 had the value 15 , then on my ResultsWorkSheet I could have
=DataSheet!A1 + Sheet1!A1
or
...

0

I belive the issue is first with your formula, you need to use AND like this
=IF(AND(P3>=1,P3<=44),"Variable Tempo",IF(AND(P3>=45,P3<=69),"Slow Tempo",IF(AND(P3>=70,P3<=94),"Slow-Medium/MidTempo","Not detected")))
Instead of showing "Not detected" you may want to simply show "" (to show nothing)
Copy and paste that down in colum Q ...

1

You can use Sumif() to sum all values in column A where column B equals a specific value.
=SUMIF(B1:B10,"a",A1:A10)

0

Eliminate col 1, place the weight in col 2, put this in col 3.
=(IF(C5="Yes"; "1"; "-1") * (IF(C5="Yes"; F5; "1")))

0

If you're not adverse to using VBA, I can get back with more detailed code later, but in pseudo-ish code:
Dim array(1, 1) As Long
For i = 1 To Len(table)
' if value is already in the array, skip this part. if not, redim the array and store the value.
If Not Match(Cells(1, i), array) <> 0 Then
ReDim array(1, i)
array(1, i) = ...

0

You can reference across Sheets, but you will need the correct syntax.
So, set up your first Sheet.
Then, on the second Sheet set up what data you want to replicate (based on the employment date being true).
You can create a similar condition for A2 to fill in the Name of employee.
When these two cell formulas are ready, you can simply copy down the ...

0

This can be done with a straightforward IF statement. In the column you want the values to be "copied" to in the second sheet:
=IF(C1<>"", A1, "")
Then autofill down. Note that the second sheet will likely have a sparse list, rows will be empty when the corresponding row in the first sheet has an empty C column.
However if you want a contiguous list ...

0

Do you mean that you want the assigned group of employees populate a drop-down in the next column? If so, what you are after is dependent data validation.
It requires a bit of prep work to set up all the products and the employees. On a helper sheet you need to create a list of employees for each product and then assign each of these employee lists a ...

0

UPDATE: I was informed by fixer 1234 that my solution was only introduced in EXCEL 2013, so it will not work for you issue in Excel 2007
I am going to leave my previous answer up however, as it would be a solution to someone searching for how to do this with Excel 2013 or later.
This should work for you:
=MID(FORMULATEXT(F16),2,10)
Result: A3 as text.
...

1

I think some of your IF logics are out (reversed).
Place all formula into one IF statement, as you seem to have only two states - Yes (1) and No (-1).
=IF(C5="Yes",1.5, -1)
That should be it?

3

Excel does not have a Sort nor a Unique function. I fail to see how you make that work. But if you have that working, however you do it, you can use a simple Sumif to add up all the numbers for the animals
=SUMIF($A$2:$A$5,C2,$B$2:$B$5)
Copy down.
Or you could use a pivot table on the original data. Four clicks. No formulas.

0

I'm a little confused about how the names and percentages are organized on your spreadsheet. Anyway you could post an image just so we can have a better idea?
by your description, I think it looks like this:
Is this about right?

0

=1*(0<sumproduct((A1:A5="Mike")*(B1:B5>=0.6)))
Change the ranges to suit your data.
This function turns (Mike, Mike, Bob, Charlie) into (1,1,0,0) and (0.4,0.7,0.8,0.2) into (0,1,1,0) and performs a sumproduct on the results.
Note that this is a volatile formula and may slow your sheet over 74k rows. Set calculation to manual if so.

0

For each function (IF and ISBLANK) you need to have a pair of parentheses,
surrounding the argument(s) to that function.
ISBLANK takes one argument: the value that you want to check
for whether it is blank.
IF takes three arguments: the Boolean (true/false) value,
the "if true" value, and the "if false" value.
So your formula should be
...

1

Cells(1, 1).Formula = "=LEFT(A2;5)"
using .Formula instead of .FormulaR1C1 solved the problem.

0

Try this one, with double quotes moved to include the colon, and a second & added after the H1s:
=SUMIF(INDIRECT("$'PC Activity'.$F" & H1 & ":$F$" & H2);"Bank.*";INDIRECT("$'PC Activity'.$C$" & H1 & ":$C$" & H2))

2

This should work:
=IF(C2="NO",IF(E2=100,Sheet2!A1,""),Sheet2!A1)
If C2="NO" then:
If E2=100 then:
Sheet2!A1
Else:
"" (blank)
Else:
Sheet2!A1

1

There are two different ways I would do this: VLOOKUP or INDEX/MATCH.
In sheet 2, cell B2, use formula
=VLOOKUP(A2,Sheet1!$A$2:$B$11,2,false)
Drag this formula down as far as you require, where:
A2 is the first account code in sheet 2 you wish to look up
$A$2 is the start location in sheet 1 with the row column indicators preceded by a dollar sign ...

2

First, you need to extract the last name from cell B1
MID(B2,FIND(" ",B2)+1,99)
Then you can use that result to perform a Match() on column A:
=MATCH(MID(B1,FIND(" ",B1)+1,99),A:A,0) (in C1 of the screenshot)
This will return the row number of the found name. If a name is not found, the cell contains #N/A. To avoid that, you can use IsNumber to return ...

1

AVERAGEIF is the best answer for Excel 2013+. For older versions, you'll need to use a third column, C.
All cells in column C (with a value to the left) have this formula: =IF(ISNUMBER(SEARCH(C$7,A1)), B1, "")
Cell, C7 just has the word desk typed out so that those formula don't need to be fixed every time you change the search.
After this, you simply do ...

0

I'm not sure if using column C is a possibility for you, but here is one option that I have tested.
In Cell C1, input the following formula: =IF(ISNUMBER(SEARCH("desk",A1)),B1,"")
Copy/paste this formula down for each row in column C
Average the amounts that appear in column C.
If column C is already used, just insert another column in its place

3

Well as a somewhat generic answer that will work for you, another way to do a condition within a function (in your case an IF inside an AVERAGE) you can always break it apart. The IF statement can also return an array and does not need to be a single value.
If you type
=AVERAGE(IF(ISERROR(SEARCH("desk",A1:A5))=FALSE,A1:A5))
and press Ctrl + Shift + ...

8

Add the asterisk * wildcard character before and/or after the word “desk”, as:
=AVERAGEIF(A1:A5,"*desk",B1:B5)
or
=AVERAGEIF(A1:A5,"*desk*",B1:B5)
This tells Excel to ignore any characters before/after the word “desk”.

1

Don't do VLOOKUP. Use INDEX-MATCH:
=INDEX(Return_value_range, MATCH(Lookup_value, Lookup_value_range, Match_type))
Return_value_range - The range that holds the return values
Lookup_value – The value you want to find in the lookup value array
Lookup_value_range – The range containing lookup values
Match_type – Exact (0), Nearest Greater Than (-1), or ...

1

Try using the if function.
Here's how I did a test:
First: 3 columns 2 rows. Fisrt row names: A1=Mike, B1=Bob, C1=Betty
2nd row enter usage values: A2=20, B2=40, C2=70
Elsewhere on the same worksheet make set of 3 col 2 rows
First row names: Mike, Bob, Betty
Second row if functions:
if function:
Double click Fx next to cell value (the one stating ...

1

Starting from other answers proposal of using SUM formula I suggest you to create two "blank" worksheets named, for example, "begin" and "end". "begin" must be placed before your first user worksheet ("user1") whereas "end" after your last user ("user99" for example); the formula, then, will be:
=SUM('begin'!A1:'end'!A1)
This way the only thing you must ...

1

Set up a table with the customer names as column labels across the top, and row labels with a list of possible characters down. Then use Sumproduct.
Consider the following screenshot. The formula in cell B10 is
=SUMPRODUCT(($B$1:$H$6=$A10)*($A$1:$A$6=B$9))

0

This can actually be done easily by setting up another table whose columns are table entries and whose rows are the same as the rows in the original table. With this setup =COUNTIF() can be used to count for a given row in the initial table how many times the row header in the new table occurs.

0

select * from table1 where table1.worktype_unique = table2.worktype_unique
AND table1.quanity<=table2.quantity_max AND table1.price <= table2.max_price;
add as many and conditions as you want.
This will detect non matching values only for manual fixing.
select * from table1 where table1.worktype_unique <> table2.worktype_unique;
Produces a set of ...

4

=AVERAGE(IF(A1:A5>=0.5,A1:A5,1+A1:A5))
Confirm as an array formula with Ctrl+Shift+Enter
To exclude blank cells -
=AVERAGE(IF(NOT(ISBLANK(A1:A5)),IF(A1:A5>=0.5,A1:A5,1+A1:A5),""))

1

You could use the following formula:
=SUM(user1:userN!A1)
For example:
If you have 99 user sheets use:
=SUM(user1:user99!A1)

1

Yes you could reference a range of sheets as follows;
A1 =SUM('user1:userN'!A1)
When you add new sheets and want to include them in the formula, you have the following options:
1) Modify your formula using the replace command. On the home tab click Find & Select, Replace. Find what: user3, Replace with: user4. Then click Replace All. If it doesn't ...

0

Powershell in a dirty way.
$xlsFile="c:\temp\your.xlsx"
$excel = new-object -comobject excel.application
$excel.displayAlerts = $false
$wbs=$excel.workbooks.open($xlsFile)
$ws=$wbs.sheets.item(1)
$ws.cells.item(1,1) = "Customer"
$ws.cells.item(1,2) = "Apples"
$ws.cells.item(1,3) = "Bananas"
$ws.cells.item(1,4) = "Oranges"
$row = 1
$reader = ...

Top 50 recent answers are included