## Hot answers tagged worksheet-function

8

Data validation will not change formatting of the cells. You will need to use Conditional Formatting to do what you desire.
Once you have your lists in place, apply conditional formatting rules to those cells which use a formula that checks for the words RESTRICT and CASCADE.
Select the cells with the lists
Create a new conditional formatting rule
Choose ...

5

In B1 enter 1
In B2 enter:
=COUNTIF($A$1:A2,A2)
and copy down.
For example:

4

Input the following array formula (Ctrl+Shift+Enter) into cell C3 and then drag it down. I extended your scenarios just a bit and it performs as expected.
=SUM(IF(LEFT(B3,LEN($A$3:$A$7))=$A$3:$A$7,1,0))

2

Enter =sqrt((b2^2)+(c2^2)) in D2,
then move back the selection (cursor) to D2.
Note that there is a tiny black square at the lower right of the selection frame.
Grab the tiny square with the mouse pointer, then pull it down onto all lines where you wish to have the formula.
... done.
Note: Works exactly the same in Excel.
Additional info:
To lock a ...

2

The number of hours will be simply the next business day at 9:00 AM minus the starting time (formatted as [hh]:mm to allow the display of >24 hour periods). The trick will be getting the number representing the next business day at 9:00 AM correct.
The formula in B2 is,
=WORKDAY(A2,1*(HOUR(A2)>9))+TIME(9,0,0)
This makes the formula in C2,
...

2

SUMIF function does a conditional sum based on values in another range, e.g. for your example you can use this formula in E4
=SUMIF(A1:A3,"X",E1:E3)

2

I'm not sure I understand exactly the effect you are after, but if you want to be able to copy (or drag to extend) a formula while keeping some reference the same, you can prefix either the column or row (or both) identifier with a $ sign.
For example, if you have in cell A1 =B3 and copy this to cell A2, it becomes =B4 because the reference is updated to ...

1

Ok, found it.
Assuming the values are in a table, and its column is named "Column1":
=REPLACE([@Column1],1,FIND(LEFT(SUBSTITUTE([@Column1],"0","")),[@Column1])-1,"")
This will produce the desired output as shown in the question.
The core of the solution is handled by this formula, which counts the 0's before the text value we're looking for.
...

1

Another approach would be to convert to decimal then back again. The HEX2DEC worksheet function will only accept 10 digits/characters as input but RIGHT will take care of that. Instantly converting back with DEC2HEX (while leaving the optional [places] parameter out of the equation) should result in a trimmed HEX value.
The formula in B1 is,
...

1

You could try applying the concatenate function before using conditional formatting.
http://blog.contextures.com/archives/2013/04/11/highlight-duplicate-records-in-an-excel-list/

1

Go back to Conditional Formatting and delete any prior rules you have created for this purpose, then (full columns A:H selected) create a new rule using the option to Use a formula to determine which cells to format. Supply the following to the Format values where this formula is true textbox:
...

1

Could it be as simple as
D2 = 1
D3 = =IF(A2<>A3, 1, IF(C2=C3, D2, D2+1))
(and drag down)?

1

Earliest due date (column C, starting at C2) can be calculated with =MIN(A2:B2), then copy this formula down across the column.
You can then get the matching headers into column D, starting at D2, with =IF(A2<=B2,$A$1,$B$1). Then, again, copy this formula down the entire column.
In case both overhaul dates are identical, this solution will always choose ...

1

The AVERAGEIFS criteria come through as AND conditions. You require an OR condition since any one row could not be both 1 and 2.
In the above, the sum of the blue/greens is 23, their count is 5 making the average 4.6. The formula in E2 is either,
=SUMPRODUCT((C2:C8)*(B2:B8=F1:G1))/SUMPRODUCT(--(B2:B8=F1:G1))
... or with the colors hardcoded as,
...

1

The only way you can keep the formula simple is if you can come up with criteria that are not mutually exclusive. For example,
=AVERAGEIFS(Pupil_Score,Race,">=2",Race,"<=3")
This works only if race numbers are adjacent.
Others have suggested array (CSE) formulas so I won't repeat that. Those formulas tend to be rather complex. If you want to keep ...

1

You can use an AVERAGE array formula, e.g.
=AVERAGE(IF((Race=1)+(Race=2),Pupil_Score))
confirmed with CTRL+SHIFT+ENTER
The "+" effectively gives you an "OR"
You can easily add extra conditions by adding IF functions, e.g. assuming "range1" needs to be "x"
=AVERAGE(IF((Race=1)+(Race=2),IF(range1="x",Pupil_Score)))
See example here

1

Awww, figured it out. I had to put in a nested IF to check if the month = 6 and date is >= 20.
=IF(AND(MONTH(P2)=6,DAY(P2) >= 20),YEAR(P2)+1,IF(MONTH(P2)>=7,YEAR(P2)+1,YEAR(P2)))
I don't like to use nested IF statements, but it seems like a necessary evil this time.

1

I would put the date of origin of the rent in a column (A), and then the full rent in a column (B). Then I would put Current Rent in column C, and I would fill the cells in column C with the following formula:
=IF(TODAY() > A1+182.5, B1/2, B1)
I use 182.5 for half a year. That way, you get the following:
A B C
11/19/2013 600 ...

1

From your description you're either paying the full rent (1-6 months), half the rent (7-9 months) or nothing at all (10+ months). You might also want to input future values but have the formula reflect that you are not responsible for the stipend until they actually move in.
The formula I've used in C2 to calculate the prorated stipend (according ...

1

If it is just three value columns, a simple way to do it would be IF tests. Say the table you show starts in A1 (A1="ID" heading). The formula for the first output value would be:
=IF(ISBLANK(D2),IF(ISBLANK(C2),$B$1,$C$1),$D$1)
Copy that down the output column. (This assumes every row in the data table is represented by a row in the output table ...

1

If your formula returns "1" or "0" then the quotes make those text values and SUM will ignore text.
Remove the quotes and use just 1 and 0 and the sum function will sum accordingly...
.....alternatively, as counting 1s is the same as summing them you could use COUNTIF to count "1"s without changing your original IF formula, e.g.
=COUNTIF(J2:J2013,"1")

1

Updated formula. Finds the cell with the first type and second type and looks for the word widget between it. If it exists return the value beside type if not return blank.
=IFERROR(IF(MATCH("widget",INDIRECT("A" & SMALL(IF(A:A="type",ROW(A:A),1000000),E1) & ":A" &
...

1

Enter a 3-dimensional formula. This will evaluate every worksheet between (and including) the start (Sheet1) and end (Sheet3) worksheets you're interested in.
=COUNTA(Sheet1:Sheet3!A1:A99)

1

You need to define each range for each sheet e.g.
COUNTIF(Sheet1!C2:C150,"*string*")+COUNTIF(Sheet2!C2:C150,"*string*") ...
If you want to be clever, you can say put each sheet in the column A, and in column B use
=COUNTIF(INDIRECT("Sheet"&A1&"!A1:A5"),"*string*")
Or whatever fits your naming scheme. Then drag it down for each count and sum ...

1

Here is the equivalent VBA code to count the number of cells containing at least one string
Sub StringCounter()
Dim r As Range, s As String, i As Long
s = "*string*"
For Each sh In Sheets
Set r = sh.Range("C2:C150")
i = i + Application.WorksheetFunction.CountIf(r, s)
Next sh
MsgBox i
End Sub
EDIT#1:
Here is the code to ...

1

The part you queried is doing as you say:
$B$1:$B$15<>""
That returns an array of TRUE/FALSE values, FALSE if each cell is blank and TRUE if it's not. In the formula an array of the relative row numbers is divided by that array - when you divide by TRUE that acts as a 1 so you just get the row number, when you divide by FALSE that acts like zero so ...

1

Hmmm. Strange and not as specified by Microsoft. I tried to reproduce the problem, but on Excel 2011 for Mac as well as on Excel 2010 for Windows it works as expected using the following formula: =COUNTIFS(A1:A9;E1;B1:B9;">="&E2;B1:B9;"<="&E3) (I used three cells for the lookup values you want to use).

1

Select the cells in column A. Create a new conditional format based on a formula (Click Conditional Formattting > New Rule > Use a formula to determine which cells to format).
Use this formula
=COUNTIF($B:$B,A1)
Click the Format button and select a format for example a fill color. Click OK.

1

Use "Remove Duplicates" option from Data tab. Select the column 'A' > go to Data tab > click on "Remove Duplicates". This will remove all the duplicated values in the column.
Now, use 'Summation' formula to sum the rest of the cell values in the 'A' column
Hope this helps.

1

As far as I know, there is no way to concatenate arrays without VBA. The concatenate function accepts only single values. For example, you can't do this:
=CONCATENATE(A1:A3)
So the problem is not the use of arrays, but the design of the built-in concatenate function.
From what I've heard, there are add-ins that can do this. MOREFUNC is one, it has a ...

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