## Hot answers tagged worksheet-function

3

For me, this works straight out of the box.
In A1 type AG1
In A2 type AG2
Then select both cells and drag them down together (You already know this but I put it in for completeness: Hover the mouse over the bottom right of the selection. The cursor will turn into a small but thick black cross. Hold the left mouse button and drag down).
If this fails you ...

3

@Dave's solution is correct for manually populating the data.
If you need a formula for arbitrary ranges/tables, use:
="AG" & ROW() - 1
where 1 is the row number of your table header (above formula will put value AG1 into 2nd row).
If you already have numbers as the value of some other formula and you want to change the display of the numbers to ...

3

There are fancy ways to do this, but a simple, intuitive way is to use a helper column. Say your example is in A2:C12. Use column D for a helper column that transforms the column A information to a readily usable form. In D2, the formula would be:
=IF(ISBLANK(A2),D1,A2)
Copy this down the column for all of the data rows. It fills in the blanks with ...

3

You're looking for a lookup function. In your case VLOOKUP would work just fine.

3

To find the min value people have voted on in the poll (the column header of the first non-empty cell), I used the following formula (in cell H2):
=INDEX(B$1:G$1, MATCH(TRUE, INDEX(B2:G2<>"", 0), 0))
To find the max value (in cell I2):
=LOOKUP(2, 1/(B2:G2<>""),B$1:G$1)

3

The equation you want is: P = (Pv*R) / [1 - (1 + R)^(-n)]
where
P = Monthly Payment
Pv = Present Value (starting value of the loan)
APR = Annual Percentage Rate
R = Periodic Interest Rate = APR/number of interest periods per year
n = Total number of interest periods (interest periods per year * number of years)
Using the variables above, the ...

2

Reference: http://www.cpearson.com/excel/Events.aspx and http://www.excel-easy.com/vba/events.html
For more google Worksheet_Change among Excel events.
Idea: In the Worksheet_Change event, check if either of the cells can be recalculated, and do it if possible.
Note that the whole tasks is a bit more complicated than filling in a couple formulas though. ...

2

Insert a row at the top and transpose the numbers across it. Then using an IF statement you can create a matrix of all the sums.
Place this formula in cell B2 and copy it to the bottom corner of the matrix.
=IF($A2=B$1, "", $A2+B$1)
The results...

2

To count the number of "38" in your array:
If you have the numbers in cells A1:A10, the formula is =COUNTIF(A1:A10,38)
It should not matter if you have blanks in between as long as they are, in fact, blank.

2

I believe this is simply (again, assuming the values are in A2:A9):
=2*SUM(A2:A9)/COUNT(A2:A9)
Mathematically-speaking, for example:
((a+b)+(a+c)+(a+d)+(b+c)+(b+d)+(c+d))/6
is equal to:
(3a+3b+3c+3d)/6
which is:
3(a+b+c+d)/6
i.e.:
(a+b+c+d)/2
Regards

1

Unfortunately, because of the way the formulas are implemented, you will need additional computational columns, unless you want to do some programming.
To use just a formula, you will need a column for the concatenated names and a column for the amounts to add together (C2/D2). Then you can use the array formula: =SUM(SUMIF(A1:A3,A8:A9,C1:C3))

1

Basically this is called Conditional formatting in Excel. Since you are talking on different thresholds for each item, it will be best to have a threshold column as well so you can change what the threshold will be in the future easily. I could go into great detail on how to do this, however, there is a great place where this is already documented at: ...

1

This example monitors user changes to cell A1The list is built in column B starting in cell B2
Put the following event macro in the worksheet code area:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim RR As Range, N As Long, v As Variant
Set RR = Intersect(Target, Range("A1"))
If RR Is Nothing Then Exit Sub
v = Range("A1").Text
...

1

In the answer below I assume that your initial data is in cells A1:C10 and don't have headers. The solution doesn't use scripting. Only Excel formulas are used.
Enter the following formula =MOD((ROW()-1),10)+1 in cell D1
Enter =MOD(INT((ROW()-1)/10),10)+1 in cell E1
Enter =MOD(INT(INT((ROW()-1)/10)/10),10)+1 in cell F1
Select cells D1:F1 and drag it down ...

1

Save the PDF to your computer and open it in Adobe Reader. Hold the Alt key while selecting it (this allows you to use marquee/table select), then copy and paste it into Excel.

1

You will need to open up VBA and create a UDF from a Module.
Type ALT-F11 to open up the VBA editor. Under your project, perform a right click and select Insert --> Module
Type the following into the VBA editor window
Function PctChange(A As Double, B As Double) As Double
PctChange = (A + B) / B
End Function
When you call the function, do what you ...

1

It would help if you would
explain what sort of logic tree you are trying to implement, and
give an example of conditions under which the formula evaluates to a value that isn’t what you want
(and say what value you want).
Also, a common technique to debug problems like this is to simplify what you have until either
it can be read and understood at a ...

1

If you changed your 14:00 to currency you will get 0,583333333333333, so if you multiply by 10 you get 5,83 €.
Number comes from division of 14 and 24. 14/24=0.58333. 24 are hours in a day.
So the answer is to change your formula to turn the fraction back into a number of hours:
=G2*24*10

1

There may be simpler ways, but I think this will work:
=SUMIF(INDIRECT("'"&$A$3&"'!$B$2:$B$102"),$C13,INDIRECT("'"&$A$3&"'!"&ADDRESS(2,5+COLUMNS($A:A))&":"&ADDRESS(102,5+COLUMNS($A:A))))
I am assuming the only part you want to make dynamic is the F2:F102
Here is another formula that should work, also. It puts the sheetname ...

1

It is not theoretically possible to generate a random (or even pseudo-random) list with a fixed average because this requires that the later values depend on the earlier values and could even force you to go back and change some of the earlier values in some cases.

1

As Catherine explained there is not such built-in function.
After you create the random data you have to go back and change the data a little bit.
So the task can be done with VBA or other programming language.
Here is a VBA solution. All it does is:
user specifies the min, max for random function and how many numbers will be created
programm generates ...

1

Consider a lottery in which the sponsor sells a bunch of tickets (let’s say 100),
puts them into a container (let’s call it a hat),
and then randomly selects a ticket from the hat to decide who wins a prize.
Now suppose there are multiple prizes;
the sponsor draws the first prize winner, then the second, and then the third.
Naturally, the winning tickets ...

1

For each row, you want to sum a range that is a subset of that row,
determined by the “Start Month” in Column B.
Let’s begin by taking your textual description of the range and expressing it formulaically.
To do this, we’ll need to convert the month names to numbers, 1 through 12.
I don’t know of a way to do that directly, but it’s easy to extract the ...

1

You may create a column to merge first name and last name to create a column for "full name".
Then, you may follow instruction from Microsoft via link below:
* Count unique values among duplicates *
(Method 1) Count the number of unique values by using a filter
(Method 2) Count the number of unique values by using functions
...

1

Your formula looks OK to me except for some issues with parentheses, try this version
=INDEX('[Profitability Analysis FY13.xlsx]Data Sheet S13'!$B$8:$EH$85,MATCH($A17,'[ Profitability Analysis FY13.xlsx] Data Sheet S13'!$E$8:$E$85,0),MATCH(1,($B17=' Profitability Analysis FY13.xlsx] Data Sheet S13'!$B$4:$EH$4)*(J14='[Profitability Analysis FY13.xlsx] Data ...

1

To get the average of the sums of each pair (except each number with itself) you can do that with a single formula if you re-create CharlieRB's transposed table within the formula like this:
=AVERAGE(IF(ROW(A2:A9)>TRANSPOSE(ROW(A2:A9)),A2:A9+TRANSPOSE(A2:A9)))
confirmed with CTRL+SHIFT+ENTER
So TRANSPOSE(A2:A9) gives you the transposed column and then ...

1

The issue is that your AVERAGEIFS formula, with an array argument, is returning an array of values, some of which are DIV/0, and others might be single values, or represent the average of several values (if there are separate values with the same status).
In order to do what you want, the following formula should work:
...

1

You can apply a wildcard to the COUNTIF criteria.
The formula in D2 is,
=COUNTIF($A$2:$A$44, "*"&C2&"*")
Fill down as necessary. If you prefer something a little different that a SUM of those values, the total can also be retrieved with the formula used in D10.
=SUMPRODUCT(COUNTIF(A$2:A$44, "*"&C2:C8&"*"))
The method you were ...

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