## Hot answers tagged microsoft-excel

4

The closest I can find (using Office 2010; I'm not sure if it still works with newer versions) is to right-click your mouse, and under Paste Options:
look for the icon that looks like a clipboard overlaid with a picture of a paintbrush (this should be the left-hand icon in the picture above).

4

Use this formula in the Conditional Formatting:
=SUBTOTAL(3,$A$2:$A2)<11
Make sure it applies to the entire dataset.
The formula returns the row number relative to the visible row number. Thus as a row is hidden the row beneath the hidden returns one greater than it would.
To see how it works place SUBTOTAL(3,$A$2:$A2) in an empty column. Then ...

3

You can achieve it with a pivot chart: insert a pivot chart, then select group and sub-group columns for axis labels and scores for values.

3

You are checking if two intervals overlap.
I will assign your Quarter from Q1-Q2, and served from S1-S2
Q1 being the first date of the Quarter, Q2 being the end date of the Quarter.
S1 being the first date of service, S2 being the last date of service
Basically you need to check
=if(and(S2>=Q1,S1<=Q2),"They stayed here","They didn't stay here")
...

3

Supposing your data is in column A, then your first four values are in cells A2 to A5. Then :
In cell B5 the formula must be =AVERAGE(A2:A5).
Select the cells B2 to B5. Just like this:
Double click the little square dot on the corner of cell B5 and the column B will be filled with the average and blank spaces.

3

Here's one approach using a helper column:
Column A is the starting row for each value.
A1: 1
A2: =A1+C1
etc.
The results are in column E. Cell E1:
=VLOOKUP(ROW(),A$1:B$4,2)
With the row ranges defined, you can do a simple VLOOKUP for the value in each row.

3

You can run the spell checker to help identify and correct typos. Invalid entries can be prevented by using data validation.
Sorting and filtering will not help with grammatical mistakes or invalid entries. Excel is a spreadsheet application. You can only process what you put in. If you put bad data in, you can't expect Excel to clean it up magically.

2

If you never want the value Active_Directory_Locations!$D5 to change then one thing that should work would be the INDIRECT function which evaluates a string value to a cell reference at cell computation time.
Try replacing all references of
Active_Directory_Locations!$D5
With
INDIRECT("Active_Directory_Locations!$D5")
As your cell location is now a ...

2

1 2 3
A 1
B 2
C 2
D 3
In cell B2, place =IF(A2=A1,0,1)
In cell B3, place =IF(A2=A3,0,1)
Pull them down
This will result in something like this:
1 2 3
A 1
B 2 1 0
C 2 0 1
D 3 1 0
Column 2 will count shifts, while column 3 will count the repeated values.
At the bottom, sum ...

2

It's not sorting, but called binning, or calculating frequencies, you need to use FREQUENCY:
The formula I've used in column E is:
=FREQUENCY($A$2:$A$21,$C$2:$C$7-1)
it's an array formula, so you need to select first all the cells it will contain it, enter the formula, then finish it by CTRL+SHIFT+ENTER
First frequency calculated is the number of items ...

1

The are at least three options:
put the material in as a Picture
put the material in as a Table
put the material in as an embedded Object
Picture is a good way to preserve format. It is easy to re-size and re-position a Picture.
In Excel, copy As Picture and then paste the Picture into your Word doc.

1

There is a good answer here.
Basically you add a helper column with the formula
=SUBTOTAL(3, $A$2:A2)
This gives you the count of visible cells between A2 and your current row. Then you would highlight column A and use a conditional formatting something like
=$E1<10 with the format you want and it should highlight the top 10 rows

1

You have a couple issues here.
Generally excel formulas don't handle arrays as inputs (if you just hit enter) so your formula is only comparing the first cell of your ranges.
If you do enter an array formula using ctrl+shift+enter it will compare against the full range but your or and and functions will simplify that down to a single true/false for the ...

1

Surely making an additional entry for remaining expenses would be the simplest method.
But
In your example.
Registration: $400
Flight $900
Per Diems $200
Hotel $400
Remaining = (budget for the year) - (sum of the above expenses)
This would give you the value which you can then work into your pie chart

1

There are a variety of approaches depending on your budget and degree of automation you expect.
Cut/paste and custom parsing
You manually open documents and copy the contents as text into some other program, which could be Excel. Somebody with programming experience then writes programs, or Excel macros, to try and extract the tables from the text, and ...

1

Unfortunately, VBA does not port to Google Sheets, but if the Google Sheets requirement is waived, it is quite simple to do with VBA.
Attach this code to the WorkSheet_Change event for the worksheet of interest ...
Private Sub Worksheet_Change(ByVal Target As Range)
Dim RngToMark As Range
' define the range you want to track changes for
Set RngToMark = ...

1

In your Word doc, you could create one bookmark and then cross-reference the bookmark text.
So you would create/set the bookmark for a particular value in one place and then (instead of creating multiple other bookmarks for the same value) at each other place you want the same value to appear, insert cross-references. In the Cross-reference dialog, just ...

1

Brock - Below is a screen capture of something that could solve part of your problem. Instead of typing all of the previous numbers into a cell, I made a small table. The results of this table can be posted into another cell, anywhere in your workbook.
Column A just contains the year.
Column B is the distribution for that year. It could contain a ...

1

You need to use a helper column where you check whether actual row contains both names:
=COUNTIF (A1:D1,"Amy")*COUNTIF (A1:D1,"Bob")>0
Then just count cells with TRUE:
=COUNTIF (E:E,TRUE())

1

This method uses the value typed in cell A1. If you enter 2 only the first two columns will be visible. Typing 3 will make three columns visible, etc.
Enter the following event macro in the worksheet code area:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim A1 As Range, rINT As Range
Dim N As Long
Set A1 = Range("A1")
Set rINT = ...

1

I guess row 1 will be a header label/name
use this formula in row 6 and copy down
=IF(MOD(ROW()-2,4)=0,"Avg = "&AVERAGE(T2:T5),"enter data")
then overwrite "enter data" cells with the data you want.

1

You need to combine IF and VLOOKUP for this.
The IF statement will check to see if you have put a value in B501 while the VLOOKUP will read the value in B501 and display its corresponding cells from your values.
In cell C501 type this:
=IF(B501,VLOOKUP(B501,A1:C500,2,FALSE),"")
And in cell D501 type this:
=IF(B501,VLOOKUP(B501,A1:C500,3,FALSE),"")
...

1

Select the cells you wish to examine and run:
Sub fourmat()
Dim r As Range, msg As String
msg = ""
For Each r In Selection
msg = msg & vbCrLf & r.Address(0, 0) & vbTab & r.FormatConditions.Count
Next r
MsgBox msg
End Sub
It will list each cell and the number of conditional formats associated with that cell:
...

1

You should enter it as =SUM(E12:E26/C12:C26) followed by Ctrl-Shift-Enter on Windows or Command-Enter on Mac.It will then be treated as an array formula and the formula bar will show it as {=SUM(E12:E26/C12:C26)}

1

You could use Sumproduct to avoid having to array-enter
=SUMPRODUCT((E12:E26/C12:C26))
Just hit Enter.

1

With no thought put into the actual stats of this I would do the following.
Formula entered as an array formula ctrl+shift+enter
=SUM(ABS(A3:A5-A2:A4))
It calculates abs(A2-A1)+abs(A3-A2)+abs(A4-A3)... and so on as long as your range is.
Could also use an average or whatever function you want but it should give you some idea of the noise.

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