## Hot answers tagged worksheet-function

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

4

I would start by optimizing your O, P, and Q formulas.
You currently have
=IF(IFERROR(SEARCH("Brand is not valid", M42), "") <> "", "Brand", "")
IFERROR is a great function for displaying a sanitized version
of a computed value that might be an error code;
I use it, and recommend it in answers on Super User, frequently.
As you probably know,
...

4

Supposing you want to put the formulas in column F
I would try in this way:
write your formula on F1 (so the first cell will contain =LEFT(E1,2) )
select and copy the cell containing the formula (F1)
select the entire F column (click with your mouse on the column letter on top)
finally paste-special/formula and your formula will be in the entire F column ...

3

Here's a single-formula solution:
=SUMPRODUCT((((C=0)+(D=0)+(F=0))>0)*(C+D+F>0))
Note that I've used just the column letter to refer to your entire data range. It'll actually be something like $C$2:$C$500 or Table1[Actual Stock] or whatever your actually data setup requires.

3

Dates in Excel are stored as integers. While you have been confused by how it is displayed in the cell, the value your function returns is indeed the correct one, you just have to change the formatting of the cell to the same as the other one, and they should display the same value.
If you have a look at the dropdown-menu for the format of the cell, you can ...

3

You can try R1C1 style references.
More info here:
https://excelmate.wordpress.com/2013/04/22/excel-r1c1-reference-style-vs-a1/
Quoting from that webpage:
First, the familiar A1 style:
And now for R1C1….
Absolute references...
In R1C1, there are no $. If I write R3C4 I am referring to $D$3. So if there are no brackets, it’s an ...

3

Formula for B1 (copy down the column):
=DATE(2014,A1+1,0)
This formula returns the date one day before the first day of the next month, which will be the last day of the specified month.
If you will be reusing the same data, stick the year in a cell somewhere and reference that cell instead of hard coding the year in the formula; just change the year in ...

2

type A1:X1 in cell A3 - press ENTER
type =INDIRECT($A$3)^2/(INDIRECT($A$3)+1) in A4 - press SHIFT+ENTER
type =SUM(INDIRECT($A$3)) in cell B3 - press ENTER
Note that to change A4's content you need to select A4 and then the entire range first! Just clicking on A4 won't work.
To change A3's content - to adjust for any change of the range:
1. Select the ...

2

In LibreOffice Calc the following works; since the two programs are mostly the same, it should work in OpenOffice Calc, too. Use the formula
=SUM(A1:X1^2/(1+A1:X1)); then hit CTRL + Shift + Enter to make it an array formula.
In general, a formula that takes single values and returns a single value (e.g., =A1 + B1 and hit Enter) can be made to take and ...

2

Sounds like the simplest solution would be to just use a VLOOKUP.
E.g. in cell B5 on your new sheet:
=VLOOKUP($A5,'Details'!$A$2:$E$1000,5)
This searches the first column (the dates) of range A2:E1000 on your original sheet for the date you've got in A5 on your new sheet, and then returns the corresponding value 5 columns across (ie column E)

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

The easy way to do this is to cheat and use a mixed absolute/relative formula. This is an array formula (enter with CTRL+SHIFT+ENTER) entered into cell B4 but it could go anywhere in row 4. It will return the row number of the one marked S.
=MAX(IF($A$1:A4="S",ROW($A$1:A4)))
When copied down, the second part of the reference B4 and A4 will increase. ...

2

If you have only a few lines to reach, then you can achieve it with nested SUBSTITUTE:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,",", CHAR(10)&REPT(" ",2),1),",",CHAR(10)&REPT(" ",3),1),",",CHAR(10)&REPT(" ",4),1)

2

You can force the format. For example, if you place any number in the cell and format the cell to:
"Four Apples";"Four Apples";"Four Apples";
The cell will display Four Apples but still retain the numeric value (as viewed in the Formula Bar)

2

I'd try something like
=SUMIF(O3:O10,">0")-SUMIF(O3:O10,">0",P3:P10)
If you are trying to sum Column O - Column P if Column O has a value.

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.

2

As I understand your situation, there are three relevant columns:
a column with a completion date (let's say it's in column L)
column M, which may contain the string COMPLETED
a column where you want your output (let's say it's column N)
You use the formula =$M2="COMPLETED", which results in TRUE or FALSE. If I understand correctly, instead of ...

2

Your request is pretty vague, so here's a pretty vague answer that will hopefully get you on the right track.
In each cell C1, D1, and E1, your formula will have to include some check for the value in A1. You can use the following formula construction and fill in the specifics for your project (i.e., replace the parts of the formula in <...>).
...

2

Set your print area to be just the table you want to print (page layout - print area - set) then in page setup select to center your data both vertically and horizontally.
If you want to see also the empty cells around your table then set the print area to be the seven column wide range and as tall as you need.

2

You question is quite confusing, however I still get it. Try using EOMONTH() like this -
(H) (I)
(1) Last Paid Next Payment Date
(2) 6-Feb-14 =IF(H2-TODAY()<1,DATE(YEAR(EOMONTH(TODAY(),6)),MONTH(EOMONTH(TODAY(),6)),DAY(H2)),"Enter what ever you want")

2

Select a cell in a column in your table that has values filled in all the way down to the bottom row of your table (if there is such a column), hit Ctrl + down, which will jump you to the bottom row.
Then move over to the column where you want to fill the formula down, hit ctrl+shift+up to select all the cells from there up.
If you have a header row or ...

2

Enter your formula in the first row.
Arrow over into the column E.
Press Ctrl+Down. If E has any blanks, you'll have to push this a few times to get to the last row with data.
Arrow over back to your formula column (in the last row).
Press Ctrl+Shift+Up. This will select all the cells above, including the top row that includes the formula you entered.
Press ...

2

Assuming that you want to put the formula into Column F:
Type F1:F30000 into the Name Box (to the left of the Formula Bar)
and press Enter. This will select that range.
Type =LEFT(E1, 2) …
… and press Ctrl+Enter
(not Ctrl+Shift+Enter).
That’s it.

2

=ISNUMBER(LEFT(A1,7)*1)
Assume you are putting data in A1

2

It is more rigorous (see my comment to the previous poster's solution) to check each character for numericalness on an individual basis, e.g.:
=COUNT(0+MID(A1,{1,2,3,4,5,6,7},1))=7
Regards

2

How about a VBA solution? This will copy all the select from the top left selected cell to the bottom right selected cell. If you select incongruous cells, this code will collected everything in between the top left and bottom right anyway. You can change that, of course. It was just easier this way. It puts tabs between the columns and line breaks between ...

1

You could use DATEDIF function to calculate duration
Syntax :
=DATEDIF(Start_Date, End_Date, Unit) or e.g =DATEDIF(A1,A2,"m").
inplace of unit replace the following to get the results
UNIT RETURNS
“Y” Returns the period difference as complete years.
“M” Returns the period difference as complete months.
“D” Returns the number of ...

1

With data in A1 and A2 like:
In A3 use:
=A2-A1
and to see the difference is a more "readable" format use:
=INT(A2-A1) & " " & TEXT(A2-A1-INT(A2-A1),"hh:mm:ss")
The integer portion of the result is whole days difference.

1

If you want to get the data out you probably want to use index,match and not address match.
Assume your categories are in column A, values in column B (data from row 1 through 15)
Max value entered with ctrl+shift+enter
=MAX(IF($A$1:$A$15=F2,$B$1:$B$15,0))
To grab the value in column C entered with ctrl+shift+enter
...

1

Based on what I have understood...You can explore VLOOKUP function. I am assuming that "X" is content of column somewhere next to the column B in P&S sheet.
Sample formula
=IF(ISERROR(VLOOKUP(B1,'P&S'!$B$1:$C$6,2,FALSE)),"",VLOOKUP(B1,'P&S'!$B$1:$C$6,2,FALSE))
B1 - Contents of cell B1 (in SLS sheet)
VLOOKUP B1 against range of B1 thru B6 (in ...

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