## New answers tagged worksheet-function

1

Damn, that was way easier than I imagined. Sumproduct alows you to do array-like manipulations on the individual values before you add them up
=SUMPRODUCT((A2:A6=A6)*(B2:B6<B6)*(B2:B6>B6-365)*C2:C6*((B2:B6)-(B6-365))/365)
The (A2:A6=A6)*(B2:B6<B6)*(B2:B6>B6-365) are the conditions
The C2:C6 are the values that are to be considered (if they meet ...

0

I've created LatexToWordEquation based on answers.
It is a Word AddIn.
The source code I've pushed to Github. Link here
If you prefer you can download the installer directly from here.
Note: This is an active download link that will immediately download the file when you click on it.
How to do it

1

I found a (partial) solution to this problem, but it may not work in your specific situation. The Office documentation for the HYPERLINK function provides a clue.
In my testing, importing a file of plain URLs into an Excel column where another column is already filled with HYPERLINK formulas that refer to the URL column results in the column with the ...

0

To return the last non-blank entry in column A:
=LOOKUP(2,1/($A:$A<>""),A:A)
To return the corresponding entry in column B:
=LOOKUP(2,1/($A:$A<>""),B:B)
Explanation: $A:$A<> "" returns an array of TRUE or FALSE
1/... converts that to an array of 1's and DIV/0's.
LOOKUP(2,... Since there is no value of 2, LOOKUP will return the ...

1

The rounding functions work at the place level. You could use:
=50*INT(A1/50)
However, Ron Rosenfeld's answer is simpler.
If you wanted to round to a multiple instead of rounding down, you could use MROUND:
=MROUND(A1,50)

0

LibreOffice and OpenOffice use a different delimiter to Excel.
You should swap the comma to a semi-colon.
=IF(G11=1;Q11;IF(G11=0;P11;""))
EDIT: As pointed out by @fixer1234 you can change the delimiter from the default ; to ,. You should check what yours is in the settings.

0

Tere are two ways to accomplish this.
Option 1 (clunky and less sophisticated, but perhaps easier to follow)
Because the MATCH function returns #N/A (or an error) when it can't find the match against a specific sheet, one solution is to wrap ISNA statements into your nested IF statements.
I have given you an example for the first 3 sheets here:
...

1

If I understand correctly, you want to test how many times a given customer has made a donation (CC or SO) previously, so this is effectively a running count of how many donations this customer has made.
Try this formula in P2 and copy down:
=IF(OR(J2="CC",J2="SO"),SUM(COUNTIFS(A$1:A2,A2,J$1:J2,{"CC","SO"})),0)
Explanation: tests if the line is a CC or ...

0

=If(And(Len(B5)>0,Len(B6)>0,Len(B7)>0),Sum(B5:B7),"")
where Sum(B5:B7) is the result you want to show and "" is just a blank value.

0

Enter this formula in cell A2
=COUNT(IF(C2:K2=$B$2:$B$6,1))
as an Array formula - Press Ctrl + Shift + Enter not just Enter after typing the formula.
And drag down column A.

0

In the past I have just used an if statement to make the cell appear empty if no data is entered into it. For example
=IF(A1>0, A1, " ")
This code says that if the number in A1 is greater than zero than copy A1 to that cell, else just keep the cell blank.

2

Here's one way you can manage it on your own. This is different than setting up an amortization payoff because it accommodates flexible repayment. If you'd prefer to adhere to a fixed monthly repayment of $111.25 or something, this is less useful. Otherwise...
Just add a line every month (I chose the 1st day of each month) and add a line every time there's ...

3

Depending on your interest rate (monthly here), you need something set up like this -
Basically, you need to calculate the debt based on the previous debt with a formula like this -
Now, if there are penalties for missing a payment, you can add those in by using a formula like this-
=(C2*$F$1)+(C2-B3)+50*IF(ISBLANK(B3),1,0)
Where the fine is $50 for ...

-1

If the value is A1 then =IF(OR(A1="CC",A1="SO"),1,0).
You can then use a sum formula to get a total

0

I used this:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column > 2 Then Cells("1", Target.Column) = Now()
End Sub
And it works wonderfully. Although, I foresee some unnecessary operations.

0

This should do it, if they are in order. If they aren't, then filter column A.
Sub test()
Dim lastrow As Integer
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Dim i As Integer
For i = lastrow To 2 Step -1
If Cells(i, 1) = Cells(i - 1, 1) Then
Cells(i - 1, 2) = Cells(i - 1, 2) & ", " & Cells(i, 2)
Rows(i).EntireRow.Delete
...

0

You can use the same formula as Gary's Student supplied using structured references:
=MIN(IF(Table1[Date]>=TODAY(),Table1[Value]))
Again, this is an array formula, and must be confirmed by holding down ctrl+shift while hitting enter

0

Say we have the short list in column A and the long list in column D, in B1 enter:
=IFERROR(MATCH(A1,$D$1:$D$21,0),"")
and copy down. Then in another cell enter:
=IF(COUNT(B:B)>0,"X","")

1

Here is a non-structured example that you can adapt to your needs. Say we have dates in column A from A2 to A24 and values in column B and we want the minimum value in column B for dates greater or equal to today's date.
Use the Array Formula:
=MIN(IF(A2:A24>=TODAY(),B2:B24))
For example:
Array formulas must be entered with Ctrl + Shift + Enter ...

0

Use one VLOOKUP formula for each eMail address and phone number, like this (after inserting blank columns C and D, the "database" is in columns E, F, G):
in C1, insert =VLOOKUP($B1,$E:$G,2,FALSE)
in D1, insert =VLOOKUP($B1,$E:$G,3,FALSE)
For efficiency, after filling in all customer data (name, mail address, phone number) in columns E to G, select the ...

2

If B1 appears to be a Date, but the formulas fail, then B1 may actually have a Text value. In that case use:
=TEXT(DATEVALUE(B1),"mmm")

0

Agree with Kyle. Maybe with a helper row which has an IF statement which could state, "paid" if balance is 0, or "fully due" if no payments received, or "partial due" if underpaid, or "credit due" if overpaid. And another with a date formula for days past due.
I think we'd all need to see a sample file to help further.

0

How about something like this?
=A2-COUNTIF(B1:B50,"yes")+COUNTIF(C1:C50,"yes")
This should work if you want to subtract 1 from the figure in A2 for each "yes" in the B column and add 1 to the figure in A2 for each "yes" in the C column.
If you just want to subtract 1 or add 1 if there is a "yes" in either column, this should be the kind of formula you're ...

1

You could also just check for blanks before running your function -
=IF(COUNTBLANK(A1:A10)>0,"Blank Detected",SUM(A1:A10))
Countblank() counts cells that are empty/blank, so it won't count a 0, so this checks if any are blank and if so, returns a message, otherwise it runs your function (sum in this example).
Per CharlieRB's answer, you can use or ...

0

The easiest option I know of is the following:
Select the colum that contains the contract numbers
At the home tab (in the ribbon) choose conditional formatting->highlight cell rules->duplicate values.
This way all values that occur twice will be highlighted, making it easier for you to identify these values. Alternatively, you could:
Again select the ...

1

Using the logical IF and OR functions will help you in this case. You can the IF statement to look if conditions are met and the OR will check if two criteria are met returning either TRUE or FALSE to the IF function.
=IF(OR(ISBLANK(A1),ISBLANK(A2)), "", A1+A2)
The OR(ISBLANK(A1),ISBLANK(A2) checks to see if A1 or A2 are blank. It either one is, then the ...

0

if you enter =35-SUM(C5:C40) in cell B1 then each time you enter you enter a number value in cells c5 to c40 the number will then countdown as needed.

0

Here it is:
Public Sub removeRows()
Application.ScreenUpdating = False
Dim wkb As Workbook
Dim wks As Worksheet
Set wkb = ThisWorkbook
filtercolumn = "G"
Set wks = wkb.Sheets("Sheet1")
totalrows = wks.Cells(Rows.Count, "A").End(xlUp).Row
For j = totalrows To 1 Step -1
If wks.Cells(j, filtercolumn) = 0 Then
...

1

Thanks Steven for your response. I already tried TRIM function but dint work, however now I have the answer to this question now, so I wanted it to share with you all. Below is the combination of 2 function, it gave me desired output.
=TRIM(SUBSTITUTE(D2,CHAR(160),""))

1

It sounds like the COUNTIF() function would be useful. You can use it
to count how many cells in a range match some value. Say an inventory ID
is in A1, borrowed item IDs are in G1:G20, and you want B1 to say how
many of A1 were borrowed. I don't have Excel on this machine, but
something like this in B1:
=COUNTIF($G$1:$G$20,A1)

3

=SUMPRODUCT(--(range<>""))
Will give the you the number of non-blank cells in the range, ignoring both empty strings ("") and empty cells.

0

Use a countif("range","name")
e.g. at N7 (count) for Claire:
=COUNTIF(A3:F22,M7)
where A3:F22 is all the data / all tables
and
where M7 is "Claire"

2

To count values use the COUNTA function.
So =COUNTA(Range)
This will give you the number of values in your range.
The opposite can be achieved using =COUNTBLANK(Range)
This will provide a count of blank cells in your range.
Cheers,

0

lookup/vlookup -- doesn't work in the way you are asking it to:
Likely Solutions:
Sort: your desired Names would be at the top.
(Best solution) Use a Filter:
Only the desired Names would appear on a filter.
Data → Filter → AutoFilter.
Then select the "key" you want to filter and Excel does the rest.
Result: A query list/table as desired.
...

1

To expand @SalvoF's comment: you need to use a formula for your conditional format.
Select your whole table (probably selecting by columns is best).
Conditional Formatting -> New Rule.
Use a formula to determine which cells to format.
Enter the formula =$E1="Complete". The $ means that it always looks at column E to figure out whether the formula is true ...

0

In order to just copy visible cells in a filtered range, you need to specify that that is what you try to do. For instance:
Sheets("Sheet1").Range("A1:B10").SpecialCells(xlCellTypeVisible).Copy _
Destination:=Sheets("Sheet2").Range("E1")
Some good examples of how to use autofilter in VBA-code can, among other places, be ...

0

Avril,see the snapshots
Enter this formula =$A2="L" , and then applies it to the range which you need to highlight, in my case it is =$A$2:$I$3

1

You have two columns to fill in. Let's start with category name in column G. Trying to do this with VLOOKUP would be convoluted because the values in column E don't match the values in column I. However, you're in luck because of your naming convention. The column E sub-category names contain the category name as the first portion, and the sequence ...

0

You need to use the function VLOOKUP.
Syntax: VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])
With Vlookup you can search for a match and bring the value in another cell of the same row of the match.
Now, in Sheet1 cell B2you can put: =VLOOKUP(A2,Sheet2!$B$2:$B$100,1,FALSE).
It will look for a match of the value of A2 in the first ...

0

Here's a general solution. I purposely positioned it in the middle of the sheet to illustrate the handling of blank columns before or after the range.
I've used some helper columns to make the solution easier to see and troubleshoot. The formulas get unwieldy if you try to do everything in one formula, although you could consolidate this into a single ...

1

Here are two solutions, corresponding to my suggestions in comments on the question. For both of these, I'm assuming A1:C30 contains the data from the question.
Using a Database Function
The first solutions uses Excel's database functions. The database functions all treat a range of cells as a database, where each row is a record, and each column is a ...

1

I came up with a new formula that works better then the original formula I came up with. This is the new formula:
SUMPRODUCT(--NOT(ISBLANK(OFFSET(B2:E2,0,1))),--ISBLANK(B2:E2),--NOT(ISBLANK(OFFSET(B2:E2,0,-1))))+SUMPRODUCT(--(SUM(C2:D2)=0),--(SUM(B2,E2)>0))
I feel more confident this picks up all the non-blank,blank{,blank},non-blank patterns when ...

7

You can use INDEX together with RANDBETWEEN to do this.
=INDEX({"New","Mint","Very Good","Good","Acceptable","Poor"},RANDBETWEEN(1,6))
Just fill this formula down your column.

10

If you only have a few strings
If your strings are in the first column you can use the CHOOSE() function like this:
=CHOOSE(RANDBETWEEN(1,6),$A$1,$A$2,$A$3,$A$4,$A$5,$A$6)
RANDBETWEEN(1,6) will randomly pick a number n from 1 to 6 - CHOOSE then counts through all the parameters you've listed (your strings in column A) and displays the nth term.
But, ...

0

This can be done through simple conditional formatting. Take a look at my example below:
First, I use the formula:
MAX(IF(A:A=D2,ROW(A:A),0))
Which will return the maximum row number to which the condition is true. In Excel if you press Ctrl + Shift + Enter after you submit your formula, Excel will treat the returned value as an array rather than a ...

0

You need to use the function VLOOKUP.
Syntax: VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])
With Vlookup you can search for a match and bring the value in another cell of the same row of the match.
To make it work, you need to exchange columns on Sheet2 so it should look like this:
Supplier Part Number Company Part Number
...

0

One way to do this is with a helper column on sheet 2. In C2:
=IF(ISERROR(FIND("screw",LOWER(B2))),"",A2)
You have screw capitalized in some cases, so LOWER compares it to the lowercase version of B2. If you might look for other items, store screw in a cell somewhere as an entry value and use a reference to that cell instead of hard coding it in the ...

5

Use this: =AND(D1<=D3, D3<=D2).
Excel computes the formula using an order of precedence: D1 <= D2 evaluates to TRUE. TRUE <= D2 evaluates to FALSE.

0

Sounds like your data is formatted incorrectly. Right click and goto format. Make sure this is either set to general or number. If it is already general then try changing it to number. If excel thinks a cell is text it won't do conditions like that correctly.

Top 50 recent answers are included