## Hot answers tagged worksheet-function

6

You just need to wrap whatever formula you're using now in an IF statement that returns 1 for the condition being met and 0 for not.
For example:
=IF(SUM(B2:B30)>0,1,0)

3

a b c d e f
0 0 0 1 0 0 d =INDEX($A$3:$F$3,1,MATCH(1,A4:F4,0))
0 0 1 0 0 0 c =INDEX($A$3:$F$3,1,MATCH(1,A5:F5,0))
Trust this helps

3

If there are always either 0 or 1 values than simple =MAX(B2:B30) is enough.

3

You have to type the first 2 or 3 numbers in and then select all of them and then drag down. eg. Type 2 4 and then drag and you will get 2 4 6 8 10 etc...

2

Type your “1”.
Drag the square (I believe that it’s called the “fill handle”)
with the right mouse button.
When you release the button, you’ll get this menu:
Select “Series…”. You’ll get this dialog box:
Set “Step value” to 2, and there you go:
...

2

So you have
=COUNTIFS('S:\Data\HQ\2014\Report.xlsx'!Table2[RFI Date],">="&B1,'S:\Data\HQ\2014\Report.xlsx'!Table2[RFI Date],"<="&B2)
Which is basically count how many rows in the table are both greater than or equal to B1 AND less than or equal to B2. This would be the same as:
=SUMPRODUCT(('S:\Data\HQ\2014\Report.xlsx'!Table2[RFI ...

2

NOTE HERE: I'm just viewing your IF() and changing the condition-statement.
If this does not work for you - it might at least give an idea of how to write/change it.
=IF(
AND(INDIRECT("LargerSheet!$B"& //Cell starting with "$B" and ending with
MATCH($A1,LargerSheet!$A:$A,1) //row of the last date for the name.
)<$B1, ...

2

If you can put the values to compare to into a range elsewhere in the spreadsheet, you can use the LOOKUP function. LOOKUP looks for its first argument in the range specified in the second argument, and returns the corresponding value from the range in the third argument. You'll have to add a range specifying the value you want.
For example, I create the ...

1

Nested SUBSTITUTEs could work for you. For example, I have a list of MAC addresses which may be formatted with one of three delimiters (colon, dash, space,) or no delimiter at all. To make these consistent, I do the following so that they're all non-delimited:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1," ",""),":",""),"-","")
SUBSTITUTE takes up to four ...

1

I suppose:
=IF(COUNTIF(B2:B30)=0,0,1)
would work too, but maybe Excellll's one is marginally faster.

1

I'm going to give you a formula that works differently from the one you listed since MATCH returns the first function.
This formula is an array formula so is entered using CTRL+SHIFT+ENTER.
This assumes your large table is in column A. The date you want to compare is in cell E2.
=MAX(IF($A$1:$A$33<E2,$A$1:$A$33,0))
For each cell in the large ...

1

Assuming you can sort the data in your LargerSheet, you can solve your problem like this:
First do a custom sort on LargerSheet; sort first by Name (A-Z) and second by Date (Oldest to Newest). Now all the same name entries are grouped together, and the last entry in each group is the most recent date for that name.
--A-- --B--
Alice ...

1

=SUMIFS(B:B,A:A,">="&DATE(year,month,1),A:A,"<"&DATE(year,month+1,1))

1

You could use the match function to see if the number is in your list. If the match function doesn't find an exact match it returns a n/a. Using this fact we can flag cells as either being in the list or not. Once you have a flag filter the rows and delete all the ones that are in your list.
=IF(ISNA(MATCH(A2,$F$2:$F$3,0)),"Not in List","In list")

1

You can use an array formula to solve this (also known as a CTRL+Shift+Enter formula).
The first thing to do is manipulate your second table so that there is a date column in its own right. (It's generally best to avoid having information held in your column headers.)
I've used Tables (Insert > Table) so it's a bit easier to read the formula.
Now in ...

1

Your formula is fine conceptually, but your range references are messed up. To refer to a range of cells on another sheet, you only need to reference the sheet name once, like so:
bil!A2:A12

1

I believe your question revolves around writing an INDEX/MATCH formula around these criteria:
INDEX the dates on the larger sheet
MATCH names only IF this date is greater than the date on the smaller sheet
less than the end of the month associated with that date
Then return 1 if criteria is met.
We can do this by nesting INDEX/MATCH inside an IF ...

1

You can use an array formula and MAX to get the last freq since this column is in ascending order, the last freq will be the highest freq:
=INDEX(A3:A6,MATCH(MAX(IF(B3:B6=A1,A3:A6)),A3:A6,0))
Note: Since the above is an array formula, instead of evaluating it with Enter, press and hold Ctrl+Shift and then Enter
MAX(IF(B3:B6=A1,A3:A6)) retrieves the ...

1

Given your above data, I'm going to make a few assumptions. If all of these are true, then an HLOOKUP can easily solve your issue. Otherwise, a more complex chain of functions may be needed.
Assumptions:
All relevant header texts in 'Sheet3'!1:1 are absolutely unique within that row.
All relevant header texts in 'Sheet3'!1:1 are of the format "[X]: [Y]", ...

1

That is the way VLOOKUP is designed to work when the Range Lookup parameter is set to TRUE. It will return the value for the nearest value less than or equal to the lookup value. If no value meets that criterion, an error is returned.
A couple of options for you:
Set an exception for values below the lower bound and let VLOOKUP handle the rest. To do ...

1

Note- going to troubleshoot this code this evening, still having some errors moving down the list of values. Not currently workable, just don't want to reformat my code lines. Feel free to play around. I will add comments and instructions at the same time.
Someone mentioned that this could be done with VBA. I like VBA, so I took a whack at it.
A couple of ...

1

Similar to what @Excellll recommended, you could add an additional column specifying the day of the week, i.e in sheet 1, where your raw data is present and columns A and B hold date and time respectively, you could use this function in column C
=IF(OR(ISBLANK(A1),ISBLANK(B1)),"",WEEKDAY(A1))
The approach that I'm using is purely based on functions. ...

1

I have found the errors in my way. I had to do the following:
Format Start Date/End Date as mm/dd/yyyy
Format End Date/End Time as HH:MM:SS (24 hr format***)
Then I was able to use the formula (in R2) =(P2+Q2) - (N2+O2)

1

Try using Formulas|Evaluate Formula, which will allow you to step through the process. I suspect the value in F2 is incorrect and therefore trying to redirect you to a cell or cells that don't exist.

1

Go to the box in the top left corner, the one that shows the cell reference currently selected. Enter B40000 in here and press enter. This will take you straight to that cell.
Next enter the word you want in that cell, then with the cell selected hold Ctrl+Shift and press up on the arrow keys. This will add all cells in that column before B40000 to your ...

1

You could do something like this:
=SUMPRODUCT($A$2:$A$11*($B$2:$B$11&$C$2:$C$11<>""))
The idea is that the concatenation of B and C is blank only if both are blank.

1

Use the INDIRECT function. In each cell, instead of using
='OME6500 Commissioning '!C2
use
=INDIRECT("'OME6500 Commissioning '!C2")
The reference will never change no matter what you do to the originating cell(s). If you need to fill down a large amount of cells, use
=INDIRECT("'OME6500 Commissioning '!C"&ROW(A2))
then you can fill down the ...

1

For the last two criteria you need the range outside of the brackets so it "<=" & B$5 instead of "<=&B$5"
Final function
=COUNTIFS('CURRENT LEAD LISTING'!$C$2:$C$378,"ACT",'CURRENT LEAD LISTING'!$E$2:$E$378,"X",'CURRENT LEAD LISTING'!$A$2:$A$378,"<="&B$5,'CURRENT LEAD LISTING'!$A$2:$A$378,">="&B$4)

1

The following module takes a range and replaces cell references with values found in the referenced cells. Uses Range.Precedents to get the referenced cell addresses to a string and Split to transform it to an array of cell addresses. This array is then used to find and replace matching text in the formula of the target cell.
Usage
Copy the entire code to ...

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