## Hot answers tagged worksheet-function

5

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

4

Two options:
Nest another IF condition in your array formula rather than using AND.
=SUM(IF(MONTH(A8:A1000)=1,IF(E8:E1000="Y",C8:C1000,0),0))
This option requires array entry of the formula (i.e., enter with Ctrl+Shift+Enter).
Use SUMPRODUCT with the two conditions built into an argument.
=SUMPRODUCT((MONTH(A8:A1000)=1)*(E8:E1000="Y"),C8:C1000)
...

3

This can be solved by using the following formula for conditional formatting:
=IF(AND(NOT(ISBLANK(A1))),NOT(ISNUMBER(A1)))
*Replace A1 with your relevant cell(s)
Make sure you select the "Use a formula to determine which cells to format" option under create a new rule to get this to work correctly.

3

In F1 enter:
=A1 & B1 & C1 & D1 & E1
and copy down.
EDIT#1:
If you want to bite the bullet and use VBA, then consider this tiny UDF
Public Function KonKatenate(rIN As Range) As String
Dim r As Range
For Each r In rIN
KonKatenate = KonKatenate & r.Text
Next r
End Function
User Defined Functions (UDFs) are very ...

3

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

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)
Under the bonnet
Lookup function has the following signature: LOOKUP( ...

3

Format B1 as Date mm/dd/yyyy and then in B1 enter:
=IF(A1="","",A1+7)

3

You are using the function properly. Excel is using error checking to warn you there may be cells which need to be included.
Excel Help states:
Formulas which omit cells in a region - A formula might not
automatically include references to data that you insert between the
original range of data and the cell that contains the formula. This
rule ...

2

Try this out. Highlight cell C3:D4 (as per example) and add conditional formatting with the following formula:
=AND(ROW(C3)-2=DAY($A$1),COLUMN(C3)-2=MONTH($A$1))
I have tested this and it works as described so let me know if you have any problems.
Note: this formula excludes year as a factor, so 1/1/2000 would highlight cell C3, as would 1/1/2015
Copy ...

2

Try replacing the SUM(COUNTIF formula with MATCH, e.g.
=MATCH(D3,{"Opportunity Qualified","KDM Needs Defined","Proposition Developed","Terms & Stock OK","VP Pitched to KDM","Reshaped & Repitched","Won Pending Invoice"},0)
You can then use that version with your IF formula - If you use a Name to name that list as List then your formula can be this:
...

2

AHA! Here's your dang monster array formula:
{=LINEST(N(OFFSET(B2:B22, SMALL(IF(ABS(D2:D22)<=B31, ROW(B2:B22)-ROW(B2)), ROW(INDIRECT("1:"&COUNTIFS(D2:D22,"<="&B31,D2:D22,">=-"&B31)))), 0, 1)), N(OFFSET(A2:A22, SMALL(IF(ABS(D2:D22)<=B31, ROW(B2:B22)-ROW(B2)), ...

2

A first crack would be
=VAR(IF(DJ4:EH4=-99, "", DJ4:EH4))
Press Ctrl+Shift+Enter after typing this,
to make it an array formula.
If your data contain any blanks, the IF(…,"", …) causes them to be processed as zeroes,
so revise that to
=VAR(IF((DJ4:EH4=-99)+(DJ4:EH4=""), "", DJ4:EH4))
using + to combine two Booleans in the sense of “or”
(because OR(…) ...

2

If you add a helper column (let's call it Z) containing WEEKNUM(A2) (as suggested by MBu),
then
=SUMIFS(B2:B8, Z2:Z8, 9)
will add the values in column B for the rows where column Z contains a 9;
i.e., where the date in column A is in week 9.

2

This is pretty broad, but essentially I think I understand what you're trying to accomplish. Let's take an example spreadsheet like this -
Title Tasks Percent Done
ch1 task1 75%
ch1 task2 50%
ch2 task3 25%
ch2 task4 75%
ch3 task5 50%
ch3 task6 25%
From what I gather, you want a summary of ...

2

You simply use the CONCATENATE function:
=CONCATENATE(A1,"+",B1)
Or you can use the concatenate operator:
=A1 & "+" & B1
In both cases the numeric cells are converted to strings automatically.
Following the suggestions in the comments, you can combine the calculation and the result in the same cell:
=A1 & "+" & B1 & "=" & ...

1

In column 3 you can use this -
=IF(ISNA(IF(MATCH(B1,A:A,0),1)),0,1)
It returns 1 if the value is found, 0 if it isn't.
Or, if you'd rather be smart about it, you can use Engineer Toast's formula
=IFERROR(1+0*MATCH(B1,A:A,0),0)
Essentially, the basis is that by using match, you are checking for the value in the list and return an error if it isn't ...

1

Say, for instance, you have your running total in Sheet1!A1. Right, so now you create a macro you can run that will take all the totals from the other sheets and sum them.
Let's assume the totals for each sheet are on cell A10 -
Sub updatethesum()
Dim ws As Worksheet
Dim i As Double
i = 0
For Each ws In ThisWorkbook.Worksheets
If ws.Name ...

1

The CONCATENATE function in Excel is frustratingly limited. As far as I can see, your only option for having a fill-able concatenation formula is to bite the bullet and enter the address of each term as a separate argument.
=CONCATENATE(A1,B1,C1,D1,E1)
Sorry, that's the best I can offer without VBA. I hope you don't have a ton of columns.

1

You are close. Enter the following formula, but instead of pressing only Enter, press Ctrl + Shift + Enter as this is an array formula.
=VAR(IF(DJ4:EH4<>-99,DJ4:EH4))

1

I have figured out an answer! I set the first formula (let's say it's A1) to this:
=OFFSET('Book1'!NamedRange,ROW(A1)-ROW($A$1),COLUMN(A1)-COLUMN($A$1),1,1)
I can then drag this formula across the range, and the formulae all work to calculate their own row and column positions relative to A1. The ,1,1 at the end is so that I get a single cell, rather than ...

1

No, you can't overcome a circular reference with a formula, you'll need VBA at the worksheet level using a worksheet_change event.
Sure, just use an if. Something like -
=if(sum(A1:A5)<0,somethingelse,sum(A1:A5)
This will do the formula (sum in this example) as long as sum > 0, otherwise it will do "somethingelse'
Or reverse it
...

1

In D2 enter the Array formula:
=IF(MAX(IF($C$1:C1=C2,ROW($C$1:C1)))=0,"",MAX(IF($C$1:C1=C2,ROW($C$1:C1))))
and copy down
Array formulas must be entered with Ctrl + Shift + Enter rather than just the Enter key.

1

Your post does specifically say worksheet formula, but I don't know Excel very well. However, as a work around (since you have had no answer yet), I offer you this VBa.
This should get you going. Please create a copy of the file first, as VBa does not have an undo option! Since you mention in your comments you understand some VBa I will hope the below is ...

1

Consider the following scenario:
To calculate the percentage of Pass you need to divide the number of Pass values by the total number of values.
The formula in F2 is
=COUNTIF(B:B,D2)/(COUNTA(B:B)-1)
In words: count how many items in column B equal the text in D2. Divide that by the total number of items in column B, but subtract 1 for the label row. ...

1

There is an easy way, but it only works if the other workbooks you intend to reference are all open at the same time. It is accomplished using the INDIRECT function.
http://www.contextures.com/xlFunctions05.html#RefWkbk
However, if the other workbooks are going to be closed (or might be closed), then it gets really tricky. You have to start tinkering in ...

1

What you're describing is a simple Linear Programming (or Integer Programming) problem. Excel has an add-in for that called Solver, that is disabled by default. You can enable it under Excel Options > Add-Ins > Manage Excel Add-Ins (Go). This adds the solver to your ribbon, under the Data tab.
Your objective for solving would be to maximize the total ...

1

Say we start with:
The following macro first copies the first column to the second sheet. The macro then begins with the first product (cell phone) and scans across the columns beginning with column B looking for an "X." When the "X" is found the owner is transferred to the second sheet.The macro then moves down to the second product (charger) and ...

1

Sure. You can use nested functions in spreadsheet formulas in GoogleSheets, Excel, or pretty much any spreadsheet application. Just replace B2 in the formula with the formula used to generate the value in B2.
=CONCATENATE("https://www.google.com/search?q=intitle:", """"®EXREPLACE(A2, " ", "+")&"""" )

1

If there is anything in A1 then use it, else use B1
=IF(ISBLANK(A1),B1,A1)

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