## Hot answers tagged worksheet-function

4

There is no built-in function for what you described. However, it does not require a VBA Macro. Try the following formula:
=(SUM(myRange) - MAX(myRange) - MIN(myRange)) / (COUNT(myRange) -2)
Replace myRange with the cell range (ie: A1:A10).

4

OK, took some head scratching, but I've got it:
Columns O, P & Q as you have them, titled Brand, Product & OEM. Turn the whole thing into a table with Ctrl-T (not required, but handy, and my column R relies on it, but you can use column references if you want)
Column R:
...

3

Your formula returns an empty string when all conditions are met. I think you want the calculation in that place. There is a stray comma in the AND() function.
According to your verbal description, the formula you probably need is
=IF(AND(ISNUMBER(E2),ISNUMBER(G2),ISNUMBER(I2),ISNUMBER(K2)),D2-E2-G2-I2-K2,0)
This will only do the calculation if all cells ...

3

You can make the first reference always refer to the 10th row in column B by using Index, like
=COUNTA(INDEX('0 Data Summary'!B:B,10):'0 Data Summary'!B109)

3

Assuming you are using Windows with Office, the value is stored in cell A1, and you want to have the formula be written in cell B1.
To start, the first 2 IF's are used to find out if the value is between -5 and 5, as they have the same value.
So that would be written as: If A1 is between -5 and 5, then return 2. Given that Excel does not have a between ...

3

Excel can only reference sheets absolutely. Unlike cell references, which can be relative and change when copied, sheet references are always absolute.
Relative sheet references have been requested as a new Excel feature several times in the past, but so far not been implemented.

2

While a two column criteria lookup is very doable, you would only be picking up the first fx value. Since there should be only one set of matching pairs, a SUMIFS function is a very efficient alternative.
=SUMIFS(C:C, A:A, F3, B:B, E3)
C:C » column from which the result should be taken (if the following two criterias meet)
A:A » column to search in for ...

2

This analysis can be easily completed by using a PivotTable to do the counting and a PivotChart to visualize.
Here is the finished product with data similar to yours.
When you set up the PivotTable, you will want to determine the ideal way to do the group of MONTH and EVENT. It is possible to get different charts depending on if they are both rows or if ...

2

Here is my solution. Index+match can be used to look up values in a table both ways. This tweaks the normal solution so it will find the x and return the column header.
=INDEX($E$1:$G$1,MATCH("x",OFFSET($E$1:$G$1,MATCH(A2,$D$2:$D$7,0),),0))
I put everything on one sheet so it is easier to see and also makes the function a bit shorter.
As per Barry's ...

2

Your formula should be:
=IF([@[CLOSED Y/N]]="y", VLOOKUP([@PRODUCT],'Data Sheet'!$A$31:$B$48,2,FALSE
'$' is the 'lock' character for excel (and most similar programs), it stops the program from changing like you described.

1

You can use an Index/Match formula along these lines:
=INDEX(B1:ZZ1,MATCH("zzzz",B1:ZZ1,1))
The Match("zzzz"...) will find the last cell with text in the range and pass its number to the Index function.
It will be faster than the Lookup approach.

1

Requirements like these are almost always incomplete. One possible formula to get the result as asked in the question is
=IF(C2="car",IF(LEFT(D2,6)="Toyota","Toyota",IF(LEFT(D2,5)="Mazda","Mazda","some other car")),IF(AND(C2="Lux Car",LEFT(D2,3)="BMW"),"BMW_Merc","some other car"))
For a better answer, you need to create a better question that describes ...

1

You're on the right track with considering the COUNTIFS() statement. Treat the text string like you would when comparing numbers, using > and < qualifiers to check whether each string begins with the section you're looking for.
=COUNTIFS('DBASE SHEET'!B:B,">"&A4,'DBASE SHEET'!B:B,"<"&A5)
where A4 contains your
"\Hierarchy\P2 PROCESS ...

1

Say we have data in Sheet1. The SKUs are in column A and B can contain any data. The SKUs are sorted:
The following macro will produce re-organized data in Sheet2:
Sub ReOrganizer()
Dim s1 As Worksheet, s2 As Worksheet
Dim N As Long, i As Long, K As Long
Dim v1 As String, v2 As String
Set s1 = Sheets("Sheet1")
Set s2 = Sheets("Sheet2")
...

1

You need to create a schema that will support your analysis. Say we organize data like:
Where the total for each row is in column J and the total for each column is in row #18. Now to find how many people picked ALL we could use:
=COUNTIF(J:J,8)
and to find how many people picked ALL EXCEPT siteA we could use:
=SUMPRODUCT((J:J=7)*(B:B=0))

1

I think the function call should be:
Target.Hyperlinks.Add Anchor:=Target, Address:="http://stprs533/tickets/list/single_ticket/" & Target.Value, TextToDisplay:="Ticket #" & Target.Value
I just changed both occurrences of c to Target, which I think will do what you're describing. The 'Object required' error is being thrown because c hasn't been ...

1

Simple answer, you cannot. A cell can either have a value or a formula. The formula can contain a number, but you have to edit the formula to change the value. You cannot see the value first, press enter and let the formula take its process, unless you are willing to go advanced and use VBA macros. Given the nature of VBA macros, (they're hard to grasp if ...

1

In A2 enter:
=IF(A1="",0,1)
and in B2 enter:
=IF(OR(B1="",SUM($A$2:A2)=3),0,1)
and copy across. Then in another cell enter:
=SUMPRODUCT((1:1)*(2:2))/3

1

With your data in A1, in A2 enter:
=MID(A1,FIND("-",A1)-1,9999)
and in A3 enter:
=LEFT(A2,FIND(",",A2)-1)
Of course these can be combined into a single formula:
=LEFT(MID(A1,FIND("-",A1)-1,9999),FIND(",",MID(A1,FIND("-",A1)-1,9999))-1)

1

Oh, you mean left, not len. No those don't work together, you need a workaround like
=SUMPRODUCT(--(LEFT(A1:A10,4)="text"))
If you need to use wildcards, you'll need something like this -
=SUMPRODUCT(--ISNUMBER(SEARCH("a***",(LEFT(A1:A10,4)))))

1

What I was talking about in my previous answer that got downvoted, in practice:
Public Function EndDayTimeM(StartTime As String, Minutes As Double)
Dim start As Date, starthour As Date, endhour As Date, minutes2 As Date
start = CDate(StartTime)
minutes2 = DateAdd("n", Minutes, 0)
starthour = 8 / 24 'working day starts at 8
endhour = 16 / 24 'working day ...

1

This can be accomplished with a vlookup formula.
Insert a column next to your foreign key column and a column next to your foreign key 2 column and use a vlookup to reference your second table (in the screenshot below the second table begins at column I)
The standard syntax looks something like this:
=VLOOKUP(Lookup Value , Table, Search Column, Exact ...

1

You are missing a parens and should start with 1:
=IF(ISNUMBER(SEARCH(180,A1,1)),1,0)
Works fine without the 'double quotes'

1

Relative sheet referencing is fairly easy
if you’re willing to use a little VBA. Create the following VBA function:
Function SHEET_OFFSET(Offset, Ref)
' Returns cell contents at Ref, in sheet Offset
Application.Volatile
With Application.Caller.Parent
SHEET_OFFSET = .Parent.Sheets(.Index + Offset) _
.Range(Ref.Address).Value
...

1

It appears that Excel treats "true/TRUE" and "false/FALSE" as magic strings when they appear in formulas -- they are treated as functions: TRUE() and FALSE() instead of strings.
If you need to count true and false in a column, you will not get a count if you use "true" or "=true" as the criteria. There are some approaches you can use to count true and false ...

1

I would recommend using COUNTIFS, which allows to use multiple ranges and multiple conditions without making the formula statement too complicated.
The general format is the below:
COUNTIFS( criteria_range1, criteria1, [criteria_range2, criteria2, ... criteria_range_n, criteria_n] )
Which for your case be would like this:
...

1

Consider:
=IF(OR(AND(A1<2.5,B1<2.5),AND(A1>2.5,B1>2.5)),1,0)

1

Say we start with:
and we already have assigned Names to A2 and B2. In the Formulas Tab, pull-down:
Define Name > Apply Names...
Make sure we hi-light both names and touch OK
and we get:
and so the Names get applied!

1

Working from excel 2010 on pc but should be similar
Couple ways.
The first is to use a table, highlight the data go to insert>table, should format it with alternating rows. You can then use formulas like =sum(Table1[myCol]) to sum the entire column
Dynamic formulas. The last row can be returned using a formula like =match(1e300,A:A,1) assuming its ...

1

I think rewording your conditions may help you see where you've gone wrong. Think of it this way instead:
If `a>0`, then `a`. Otherwise, check if `b=#N/A`.
If `b=#N/A`, then check if `c=0`. Otherwise, `b`.
If `c=0`, then `d/61`. Otherwise, `c`.
Note the difference between the first line and what you described. You jumped straight to "use b" and ...

Only top voted, non community-wiki answers of a minimum length are eligible