## New answers tagged worksheet-function

0

INDEX function makes this easy to do. Let's assume that you don't want to reference any columns further left than column Z (adjust as required) and that the start cell for the first formula is A2 (adjust as required) then you can use this formula in A2 copied down
=SUMIF(INDEX($B$2:$Z$5758,0,ROWS(A$2:A2)),">1",N$2:N$5758)
INDEX with zero as the row ...

2

You can try something like this
It adjusts the column value based upon the row position, but you can adapt it to your starting position
in A2
=SUMIF(INDIRECT(ADDRESS(2,ROW()+1)&":"&ADDRESS(5758,ROW()+1)),">1",$N$2:$N$5758)
If column N needs adjusting also for the sumif do the same with that
in A3
...

2

Updated, VBA answer
Under the Developer tab (if you don't have it, go to Options, Customize Ribbon, and check it) insert a Command Button from the ActiveX section.
Place the button, right click, View Code. Type this:
Private Sub CommandButton1_Click()
Call Rawr
End Sub
Then, still in the VB environment, click Insert, Module. In your new module:
Sub ...

2

It may work to keep using the count if, but instead of keeping the range the same make the range be from the first row of data until the row you are in.
A1--john S -- =countif(A1:A1,A1) -- =1
A2--Betty G -- =countif(A1:A2,A2) -- =1
A3--john S -- =countif(A1:A3,A3) -- =2
A4--Mike Z -- =countif(A1:A4,A4) -- =1
A5--John S -- ...

1

Assuming that the values in A and B are only Booleans,
=IF(A1=B1, "", IF(A1,"Pass","Fail"))
is all you need.

1

Pretty straightforward transformation of
If A is true and B is false, return "Pass" If B is true and A is false, return "Fail" If both A and B are false, return "" If both A and B are true, return ""
I assume the values in Column A and Column B are only Booleans(true/false)
=IF(AND(A2,NOT(B2)),"Pass",IF(AND(NOT(A2),B2),"Fail",""))

0

I was able to build the reference to the range I need using INDIRECT:
AVERAGE(INDIRECT("B" & ROW()-DaysToCount+1 & ":B"&ROW()))

0

MS Query is kinda passe now - I would use the Power Query Add-In for this.
For many scenarios you dont need to write a line of code, you just click around in the Power Query UI. At each step it shows you the result of your data transformation, making for productive testing/debugging.
I'll try to find some time to look at your sample file, but for some ...

0

I would use the Power Query Add-In for this. It has a Merge command that can handle this requirement without writing a single formula or any code.
http://office.microsoft.com/en-au/excel-help/merge-queries-HA104149757.aspx?CTT=5&origin=HA103993872
A Query can start from an Excel table, and you can Merge and re-Merge as required. You havent provided ...

0

Let me make sure I have this straight:
You have a lot of data in Column D that you want totaled.
Rows 1 & 2 are header rows, and contain text.
Rows 3 and below, in column D, contain values to be totaled.
Below the data to be totaled, there is a cell in which you want the total to be placed.
There may or may not be blank cells between the data to be ...

0

There is an easier way, dynamic, without formulas:
Use a pivot table. Make sure the data has a row with labels in row 1. Click anywhere in the data table and use Insert > Pivot table. Drag the grade into the filter area and the name into the row area. Now you can select a grade in the filter and the names will show in the pivot table.

0

I believe I have come close to answering your question, but it would still need some tweaking. It uses MS Query to query the workbook from the workbook. This allows you to run SQL against the sheets and perform much more complex calculations than VLOOKUP.
Now, on to the steps.
1. In the Ribbon, on the Data tab, in the Get External Data section, click ...

0

A colleague and I have come up with a better solution than nesting all the IF's together.
We came up with =SUMIF($B7:$AI7,AB9,$B8:$AI8) which works much better as no need for the multiple nested IF's.
I've expanded it further allowing for a default department and to include S (Sick) and H (Holiday) time which adds to the total hours for the default ...

0

The easiest way I can think to do this is to create a table with your data.
The table name is located by choosing any cell in the table then click the design tab in the ribbon. The table name will be in the upper left corner.
Then use a formula to refer to the table and the column header.
=COUNTIF(Table2[Smartphones], 0)
As you add columns and ...

1

Excel formulas handle the special error values of #N/A and #DIV/0 differently than other (real) values. Depending on the formulas you use, they get returned as the result no matter where they show up. So, your HLOOKUP is returning #N/A when it can't find the value, and even though you are trying to skip evaluating the HLOOKUP in the IF statement and return ...

0

The short way is something like this. Let's assume that this formula will be in B7:
=AVERAGE(OFFSET(B7,(-1 * (DaysToCount-1)),-1,DaysToCount))
I don't always like that because of the explicit cell reference there. But getting rid of that takes a bit more effort. You can replace it with one of the techniques here.

0

I would use the Power Query Add-In for this. It has a Group By command that can calculate the Max value for each bidder.
http://office.microsoft.com/en-au/excel-help/group-rows-in-a-table-HA103993875.aspx?CTT=5&origin=HA103993930

1

I would use the Power Query Add-In for this. It has a Merge command that can handle this requirement without writing a single formula or any code.
http://office.microsoft.com/en-au/excel-help/merge-queries-HA104149757.aspx?CTT=5&origin=HA103993872
I would start a Query based on your Application table. I would add an Index column (row number).
Then I ...

0

I would use the Power Query Add-In for this. It has a Merge command that can handle this requirement without writing a single formula or any code.
http://office.microsoft.com/en-au/excel-help/merge-queries-HA104149757.aspx?CTT=5&origin=HA103993872
I hope it's not too late to switch - only 5 hours have passed so you are probably still editing vlookup ...

0

You need to use =vlookup() for that. Copy column A from the bigger sheet into Column A of a new sheet. Then us =vlookup(A1, [Worksheet1.xls]tab1!$A$1:$D$25000, 2, false) to build Column B. Rinse and repeat for the remaining columns changing up values in the vlookup function as needed.

0

Not sure if this is what you're after, but I would copy and paste the entire contents of both worksheets into one worksheet or new workbook, sort, and then use Excel's delete duplicates function, checking the column labels you want Excel to use for removing duplicates.

0

Via trial and errorâ€¦
{=SUM(B2:I6,$B1:$I1)}
returns an error #VALUE!
{=SUM(B2:I6,$B1:$I1)}
returns the sum of the totals
{=$B$1:$I$1*B2:I6}
returns 180, 0, 200, 40, 40 (which I cannot explain). This was what I thought was strange.
{=$B$1*B2:B6+$C$1*C2:C6+â€¦+$H$1*H2:H6+$I$1*I2:I6}
returns the correct totals for each row.
Alternatively, I previously ...

0

The problem with your INDIRECT formula is that you're using the dollar signs to fix the cell, which means when you drag the formula around it keeps pointing to the same cell. I'll explain a little bit more in depth below.
There are three options for cell references in Excel formulas: relative, fixed row, fixed column, and fixed row and column.
A relative ...

0

Excel's array functions are very powerful tools, which come in handy with your question too. I assume column A has the bidders, B had the bids and D has the unique names.
Then in E1, put: =MAX(IF(A:A=D1;B:B)) then press CTRL>+SHIFT+ENTER, which is the way to let Excel know you entered an array function instead of a normal function.
On a final note: array ...

3

If you mean TRUE/FALSE instead of "TRUE"/"FALSE" (the former set being Boolean values, the latter being strings), I personally prefer the former, and would keep any outputs to a Boolean format as well wherever possible. It's useful to keep these as Boolean, because it makes writing formulas based on them (and returning them) a lot easier. I'll give solutions ...

10

This sounds like a straightforward AND condition.
=IF(AND(R1,S1),"Yes","No")

0

I would use the Power Query Add-In for this. It has Split and Combine commands that can convert delimited text e.g. 0,12,13,14 into a list and back.
I've built a prototype which you can view or download - its "Power Query demo - compare single value in one cell with multiple values in other cell.xlsx" in my One Drive:
...

1

=IF(D77>D69, "â†‘","â†“")
Alt codes will do the trick! The codes can be checked here for some variations!

0

Use:
=IF(D77>D69, "#","$")
and format the cell with the Wingdings 3 font.

0

Write your Math formulas in LaTeX â†’ Transform the LaTeX formulas in MathML Code â†’ Copy/Paste the MathML Code in Word (after paste click CTRL and then T). Voila!
EXAMPLE:
Lets take for example this Formula:
This is the LaTeX source Code from the above Formula:
0 \leq \lim_{n\to \infty}\frac{n!}{(2n)!} \leq \lim_{n\to \infty} \frac{n!}{(n!)^2} = ...

1

The way I would go about it would be to start by breaking up the problem into a bunch of columns, each with a piece of the problem. For example:
A B C D E F G H I J K L M N
1 12 0,12,13,14 2 5 8 0 12 13 14 Yes Yes No No Yes, Yes, No, No
2 101 101,102,103,104 4 8 12 101 102 103 ...

1

This does exactly what you want.
Option Explicit
Sub DoTheThing()
Dim row As Integer
row = 1 ' WHAT IS THE STARTING ROW
Do While (Range("A" & row).Value <> "")
Dim vals() As String
vals = Split(Range("B" & row).Value, ",")
Dim lookUpValue As String
lookUpValue = Range("A" & row).Value
Dim result As String
...

1

Perhaps Iâ€™m misunderstanding the question,
but this seems like it can be done with very simple function(s).Â
To find the row in Column B where the value in C1 appears, use
=MATCH(C1,B$1:B$1000000,0)
If cell A1 contains 1, A2 contains 2, etc, then youâ€™re done.Â
But, if you need to actually retrieve the value from A642, use
=OFFSET(A$1, (the above)-1, 0)
...

0

Found a workaround.
2nd post in linked thread.
As I mentioned in OP, if I tried making Sheet2 B1 =SUM(Sheet!J:J), and then copy that to the right, across columns, it would properly continue the formula to match C1 =SUM(Sheet1!K:K), D1 =SUM(Sheet1!L:L, etc). The problem was, however, that if tried to copy the formula downwards, across rows, it would ...

0

You can use Nate Bergeron tip and it will work.
However I think you should use =DAY(), =MONTH(), =YEAR() etc. functions instead of formatting cells to display only month.
These functions return numbers when you use cell for something, full date is not stored in them.

0

To work with fields that are formatted as a Date type you can always use the Day() function:
So ammend your formulas to include =IF(DAY($AT$8)>DAY($AU$8)... and so on.
As an example the DAY function returns the day of the month, which I believe is what you are after, like so:
26/07/2014 | 26 (=DAY(A1))
27/07/2014 | 27 (=DAY(A2))

0

Encapsulate your formula with the "TEXT" function. For example, =TEXT(YourFormulaHere,"yyyy/mm/dd") will return 2010/03/30. It will convert the generic number 40267 (your result as mentioned above) to a readable date format.
You can use any standard date formatting, just like you would in the cell format properties. Here's a link to see what your options ...

0

This can be done using the INDEX function. To increase the confusion about this function, Microsoft chose to implement two different types of this function. We're going to use the "array"-variant. More info on this function here. This function enables us to select a matrix (your Sheet 1) and choose a row or a column from it. The INDEX function returns a ...

0

I would use the Power Query Add-In for this. I've built a prototype which you can view or download - its "Power Query demo - average movement and difference across a row.xlsx" in my One Drive:
https://onedrive.live.com/redir?resid=4FA287BBC10EC562%21398
Power Query has an UnPivot command to turn rows into columns (which ignores empty cells) and you can ...

1

If you want a VBA solution, copy this into a module.
Function Score(R As Range, Col As String)
Dim ThisCell As Range
Dim Dif As Integer
Dim Cnt As Integer
Dim PosMove As Integer
Dim NegMove As Integer
Dim PrevNum As Integer
Dim ThisNum As Integer
PrevNum = 9999
For Each ThisCell In R.Cells
If ...

1

I would use an if statement to check if the cells are filled
=IF(COUNT(B2:B3)=2,B3-B2,"")
If you then average the row the blank cells will get ignored and you should get the value you want.
For your table below. Average change would be
=SUMPRODUCT(--(A2:C2<>""),--(A3:C3<>""),(A3:C3-A2:C2))/SUMPRODUCT(--(A2:C2<>""),--(A3:C3<>""))
...

0

TL;DR it's not possible as described (cf. this post on SO).
Long(er) answer: there's a workaround. The user-defined function must return a Range not an array of value. So the solution is to use a disposable worksheet to "paste" temporary data.
Since avoiding temporary data is the exact reason for which I wanted to use an UDF, that's too bad, but it can be ...

1

I don't doubt the truth of this sentence but I struggle to follow the logic: "This is mission-critical software, and its way too complex for any sane person to try and touch it.".
Anyway ...
I would try to separate this into two Excel files. One would gather the data using the VBA plugin, and would only be updated on machines with "terminal access". ...

0

I had this same problem, and my cell was set on Text.
Once I put the formatting back to General it worked fine...

0

I would resolve this with the Power Query Add-In. It takes a few steps to get there and a bit of coding in the Power Query language (M) to generate the "running count" needed to get the "cost1/2/3" column headings, and more M code to call the Table.Pivot function (it's not exposed in the Power Query UI).
I've built a prototype which you can view or download ...

0

In sheet 1, you have number, name and color in columns A, B and C. I suppose number is unique. Then, in sheet 2, you want to enter a number (say in cell A1) and in cell B1 you need the matching name and in C1 you need the matching color.
This can be done by putting the following formula's in sheet 2:
In B1: =VLOOKUP(A1; Sheet1!$A:$C; 2; FALSE)
In C1: ...

0

Try this:
=CONCATENATE(IF(COUNTIF(P:Q,TODAY()),"Action","No Action"),IF(COUNTIF(O:O,TODAY())=COUNTIF(P:Q,TODAY())," Completed",""))
Some stylistic notes:
I generally prefer the CONCATENATE function instead of smashing output strings together with ampersand. However, the formula should work with either.
For references to cells in the same worksheet, it ...

1

Your question isn't clear if you actually have hyper links or not (some are coloured, some are underlined and some not)
I have no idea if this can be done with worksheet functions, but this VBa does it.
Option Explicit
Sub CrazyAirlines()
'************** There are things you may need to edit here
Dim currentRow As Integer
currentRow = 1 'I assume we ...

3

Two options:
=concatenate(expression1, expression2, ...)
or
=expression1 & expression2 & ...
The result will be the same from either of them.
If you wish to format any of the expressions then have a look into TEXT(expression,format)
which allows the same as Custom inside Format cell (CTRL+1).
NOTE: as always the , between expressions may be ; or ...

0

would resolve this with the Power Query Add-In. It takes a few steps to get there and a bit of coding in the Power Query language (M) to call the Table.Pivot function (it's not exposed in the Power Query UI). But that's 1 line of code (the rest was built by clicking around in the Power Query UI), vs around 20 lines of VB code.
I've built a prototype which ...

Top 50 recent answers are included