## New answers tagged worksheet-function

0

Use VLOOKUP:
=VLOOKUP(I6,$C170:$G999,5,FALSE)

1

This will iterate as you drag it down.
The first reference B19 will increase by 17 every ten rows.
The second reference A25 will iterate by one, but will jump 8 every ten rows.
=IF(COUNTIF(Power!A:A,INDEX('Item Breakdowns'!B:B,19+(QUOTIENT(ROW(1:1)-1,10)*17))),INDEX('Item Breakdowns'!A:A,ROW(25:25)+(QUOTIENT(ROW(1:1)-1,10)*7)))

0

Instead of tagging column B cells to identify their column A counterparts, this functionality is built into Excel's custom sort.
Highlight both columns, click 'Sort & Filter' > 'Custom Sort...'. In the popup, you can add different sorting levels to do what I think you're trying to do. Sort first by column A, then add a new level to sort by column B.
...

0

By using SUMIF, Here you go your example
Your Example
you can use this formula to an empty column corresponding to phone numbers column
=SUMIF($B$5:$B$14,E5,$C$5:$C$14)

1

You need to bracket the dates with greater than and less than:
=SUMIFS(Spending!C:C,Spending!D:D,C1,Spending!A:A,">=" & DATE(YEAR(D1),MONTH(D1),1),Spending!A:A,"<=" & EOMONTH(D1,0))
For reference Spending sheet:

0

The following formula will work, but only if you change some of your formatting.
Formatting changes needed:
The summations cannot happen within the range of cells you want to calculate. So you can switch your formatting to something like this
]1
Once you have your summations outside the main body, the following formula will work just fine. It would be ...

0

In short, there is no way to do this without modifying your chart layouts that isn't better than specifying far more cells than you will ever use. In my example above, I end at cell J500003. Suppose your charts end at row 470. This is not a problem. Empty cells count as 0 and do not affect the sum. If you make the formula arbitrarily high to start with, you ...

0

I found a solution using VBA. Here is the Code for anyone interested:
Note: I haven't used VBA in years, so my Code might not be very good. Any Suggestions for Changes are more than welcome.
First I get the Startdate and and the Enddate from named Cells
Dim MAnfang As Long
MAnfang = Range("Monatsanfang").Value2
Dim MEnde As Long
MEnde = Range("...

0

There are three steps to this problem
use an if error in case vlookup fails, otherwise use vlookup
have the iferror output K14 if there is something there
else a blank if nothing in k14
original
=IF(K14<>"",VLOOKUP(K14,Sheet132!$A$2:$E$333,3,FALSE),K14)
Modified
=+IFERROR(VLOOKUP(K14,Sheet132!$A$2:$E$333,3,FALSE),IF(K14="","",K14))

3

This is just how EXCEL behaves. You can see the behaviour quite simple by:
make sure Cell A1 is blank/empty.
put this formula into B1: =A1
note how B1 shows a value of "0".
put this formula into C1: =IF(ISBLANK(A1),"",A1)
You can use that similar ISBLANK check in your original query.
=IF(K14<>"",VLOOKUP(K14,Sheet132!$A$2:$E$333,3,FALSE),if(...

0

The length shouldn't be a problem - this is a very common activity that I have had to do many times during my career. I use SUMIF function as follows:
(1) grab the entire 1st column and put it in a new worksheet (col A) and sort it A-Z.
(2) then put in a 2nd column B =IF(A1=A2,1,0) and autofill formulae down.
(3) filter out all the '1' values and delete ...

0

Three solutions:
(I’m assuming that your data are in cells A1:B22.)
1. Conditional Formatting
Set C1 to =B1.
(If your data don’t begin in column 1,
and the row before the first data row doesn’t have numbers in it,
you can use the next formula for the entire column.)
Set C2 to =IF(A1=A2, C1+B2, B2) and drag/fill down to C22.
This will set column C to be a ...

-2

=MAX(0,(your-formula-for-calculating-difference))

0

To get the last word:
=TRIM(RIGHT(SUBSTITUTE(A1, " ", REPT(" ", 100)), 100))
To get the first word:
=TRIM(LEFT(SUBSTITUTE(A1, " ", REPT(" ", 100)), 100))
These formulas substitute every space with 100 spaces and then return the last (or first) 100 characters. The TRIM function removes the unwanted white spaces.
Edge cases where this would not work
...

0

Use the Consolidate feature to "summarize and report results from data on separate worksheets, you can consolidate the data from each separate worksheet into one worksheet (or master worksheet)".
This feature is found in the Data Tools group on the Data tab.
Sounds like your data is formatted the same on each sheet, so you should be able to consolidate ...

0

In B4:
=MIN($F4,$J4,$N4)
Then in C4:
=INDEX($F4:$Q4,MATCH($B4,$F4:$Q4,0)+COLUMN(A:A))
And copy over through E4

1

you could use the formula:
=MIN(IF(COUNTIF(D4:AB4,">0")<3,C4,IF(COUNTIF(D4:AB4,">0")<=5,SUM(IF(D4:AB4>0,D4:AB4-36))/COUNTIF(D4:AB4,">0"),SUM(IF(COLUMN(D4:AB4)=LARGE(IF(D4:AB4>0,COLUMN(D4:AB4)),ROW($1:$5)),D4:AB4-36)/5))*0.9),20)
This is an array formula and must be confirmed with ctrl+shift+enter!
This fomula simply can be copied ...

0

I don't know how you do this with a worksheet formula, but as you've not got an answer this VBa will do it
Option Explicit
Sub WalkThePlank()
Dim column As String
column = "A" 'Update this to ye starting col
Dim row As Integer
row = 1 'Aye cap'an, this be your first row
Dim badLetters As String
badLetters =...

1

One question at a time.
For the first one, try Countif() like this for a count of the value.
=COUNTIF(A:A,"*"&A1&"*")
If you want to mark the rows where the value appears, try in B1 and copy down
=IF(ISNUMBER(SEARCH($A$1,A7)),"found","")
For the other issue, please open a new question and explain what you want to achieve.

1

This can be accomplished with conditional formatting on the TRUE cells in latest_action. All these formulas are created within a table, see this link if you're not sure how to make one, and this link for how I did the cell references.
My formulas in each column are:
count: =COUNTIF([membership_number],[@[membership_number]])
sub_rank: =SUMPRODUCT(([...

2

I think the formula =RIGHT(A2,1) will do what you want. If there might be a space at the end of the text, you could use =RIGHT(TRIM(A2,1)) to ignore the space.
If you want something even more understandable, format your data as a table. Select all your data, go to Home→Styles→Format As Table, and make sure the "My Table has Headers" checkbox is selected. ...

0

I used the following code to solve my problems:
I created AvivoWB = ActiveWorkbook and used this in the code.
Like my expectation i needed to change the directory = "c: est\" into directory = GetFolderName() & "/"
Sub Import_Excel_sheets()
Dim directory As String
Dim fileName As String
Dim sheet As Worksheet
Set ActivoWB = ActiveWorkbook
Application....

0

Everyone is using a lot of VBA code or complicated functions for this. I have a method which takes a second to implement but is far more understandable and very easy to adjust depending on various other possibilities.
In the example you've given above, paste these (4) functions into the cells, E2, F2, G2 and H2, respectively (the F&G functions reference ...

2

You can nest two IF statments:
=IF(ISNUMBER(SEARCH(" el ",A1)),"M",IF(ISNUMBER(SEARCH(" la ",A1)),"F",""))

0

It sounds like you have a set of data that needs to exist in multiple spreadsheets, but you want to maintain a single master copy.
The straightforward way to do this is to "Paste Link."
Open the master copy, select the range of cells, and hit Copy. You can future-proof this a little by selecting currently empty cells that you expect to be populated in the ...

-1

I guess this link can be useful - guys have the same question about measuring the area of loop. Try it https://www.mathworks.com/matlabcentral/newsreader/view_thread/296817?requestedDomain=www.mathworks.com
And good luck!

1

Sorry for the noob response! I have edited this post into oblivion!
Try Copy & Pasting the following into Cell L6:
=IF(OR(ISBLANK(J6), ISBLANK(K6)), "", K6+J6)
As one of the commenters pointed out, if one of the cells are formatted a text value as opposed to General/Short/Long date, you will get an error. One way to override this is by converting the ...

1

All you need is to format the column as time (hh:mm:ss), then set every element to =(ROW()-2)/86400.
This works because times are stored as fractions of a day, which has 86400 seconds in it. The =ROW() function returns the current row.

1

So in E3 put:
=E2+TIME(0,0,1)
Then copy down. It will add one second to the value above it.

1

This can be done with SUMPRODUCT and SEARCH:
=SUMPRODUCT(--(ISNUMBER(SEARCH(A1,Sheet1!$A$1:$A$10))))
The main problem is that it will pick up mobile twice as the search requires a partial match.

0

You can get it with the following array formula (press CTRL+SHIFT+ENTER after entering it in the cell:
=INDEX(C:C,MAX(IF(OFFSET($B$1,0,0,MATCH(E1,A:A,0)-1)<VLOOKUP(E1,A:B,2,FALSE),ROW(OFFSET(B1,0,0,MATCH(E1,A:A,0)-1)),"")))
How it works:
OFFSET($B$1,0,0,MATCH(E1,A:A,0)-1) - gets a reference of B column over the ID being searched
IF(OFFSET(...)<...

0

In the example, the column with the result is G, but what's the correct row? If we can find the row, then INDIRECT("G"&row) will get the result.
A solution is to add a column of pointers as column H. Each pointer is the row for INDIRECT and VLOOKUP can find it. Cell C2 has the formula.
=INDIRECT("G"&VLOOKUP(B2,$E$1:$H$9,4,FALSE))
So now, ...

1

REPT does what you need. It appends enough 0 until there are 8 characters. Add that to the 2 initials, and the total length is 10.
=IF(OR(A1="yes",A2="yes"),LEFT(B1)&LEFT(B2)&B3&REPT("0",8-LEN(B3)),"")
Napoleon Solo's System id is too long.

0

Cell A1: Contract Start Date
Cell B1: Contract End Date
Cell C1: Month index when contract began:
=YEAR(A1-15) * 12 + MONTH(A1-15) + 1
Cell D1: Month index when contract ended:
=YEAR(B1-15) * 12 + MONTH(B1-15) + 1
Cell E1: Pay per period assuming $1000 total contract
=1000/(D1-C1)
Cell F1: Number of months in 2016
=24205 - C1
Cell G1: Total 2016 pay
=...

0

Dave simplified Mikey's question so it's not so open-ended. Let me restate it. We know when a contract begins and ends. There are rules for deciding when the Revenue Phase starts and ends. We want to find two things:
How many months are there in the revenue phase, and
How much revenue occurs in 2016.
I'm going to find the revenue that occurs in the same ...

0

Use this formula from B4:
=IF (COUNTIF (A1:A3,A4)>1,"+","-")

0

You cannot combine ranges by sheet names.
You either need to have all the data on one sheet (why split it?), or write code that (one-by-one) searches through the sheets, and combines the result(s)

3

In general, you can find the last occurrence of a particular entry by using a characteristic of the LOOKUP function and a normally entered formula.
If the LOOKUP function can't find the lookup_value, the function matches the largest value in lookup_vector that is less than or equal to lookup_value.
So, using Gary's Student data as an example, you could ...

3

Say we want to find the last occurrence of "goodforyour" in column B. Try the array formula:
=MAX(IF(B:B="goodforyou",ROW(B:B)))
Array formulas must be entered with Ctrl + Shift + Enter rather than just the Enter key.

1

In D2 put this array formula:
=IFERROR(INDEX($C$1:C1,MATCH(2,IF($A$1:A1=A2,1))),"No Previous")
Being an array it needs to be confirmed with Ctrl-Shift-Enter instead of Enter when exiting Edit mode. If done correctly then Excel will put {} around the formula.
Then copy down.

1

Use SUMPRODUCT and LEFT or MID to find the values and sum:
=SUMPRODUCT(--LEFT($A$1:$A$3,FIND("/",$A$1:$A$3)-1))&"/"&SUMPRODUCT(--MID(SUBSTITUTE($A$1:$A$3,"/",REPT(" ",99)),99,99))&"/"&SUMPRODUCT(--MID(SUBSTITUTE($A$1:$A$3,"/",REPT(" ",99)),198,99))
The substitute is splitting the three numbers by 99 spaces so we can estimate where the ...

1

Does the cell need to be to contain "" or just appear blank? Can you use formatting to hide the zeroes?
Something along the line of
General;-General; or 1.00;-1.00;
Generally number before the first ; gives formatting for positive number, before second ; formatting for negative numbers and after second ; gives formatting for 0. If you leave this third ...

2

No it doesn't exist.
The usual workaround is to use a (potentially hidden) helper column.

1

Here you go:
=SUM(SUMIF(A1:A10,">0")-B1*COUNTIF(A1:A10,">0"))
Use COUNTIF to count the number of values greater than 0 in the range A1:A10 and then multiply that by B1.

0

You can't enter complex expression in MATCH, use SUMIFS or AVERAGEIFS (as you're looking for one value both will give the same result):
=SUMIFS (speed limit column, road id column, road_id,start pos column, "<=" & position, end pos column, ">" & position)

1

You could do something like this:
=SUM(SUMIF(A:A,"ABC-" & {3,4,5},B:B))
This will perform three SUMIFS and sum the results.
Edit:
To be a little more dynamic, you could put the start and end points in two cells then use this array formula to get your answer:
=SUM(SUMIF(A:A,"ABC-" & ROW(INDIRECT($D$1 & ":" & $D$2)),B:B))
Being an ...

1

My company, iPushPull, has an Excel add-in that lets you share ranges of cells between desktop Excel spreadsheets running on different machines via our cloud service.
Once you have configured your source data sheet, whenever it updates, any linked sheets will also update live. You can also view the data on the web.
There is a free trial and a free tier for ...

0

There are various ways this can be achieved.
If you use Remote Desktop to connect to your work pc from home, you can enable Remote Drive Mappings, so your local folders and files become available through virtual drives on the work pc.
Another option is to use VPN to connect the home pc to the work network and use the network to link the files.
Lastly, any ...

1

You can fill the following formula across a row to get your alternating data in one row.
=INDEX($A$1:$E$2,IF(ISODD(COLUMN()),1,2),INT((COLUMN()+1)/2))
Just specify the starting data (both rows) in the first argument of the INDEX function. The rest just uses a divisibility trick to make the entries alternate between the two columns.
Example:

Top 50 recent answers are included