## New answers tagged microsoft-excel

0

You can get the details from the link
[BOM - SAP SD |http://www.sap-img.com/sap-sd/what-is-bom-referring-to-sap-sd.htm]

0

It is pretty easy to do with conditional formatting using the below steps. You start by applying the conditional formatting to one cell and then applying it to every cell in the range.
Select one cell in one of the rows you are wanting to apply the formatting to.
Click on Conditional Formatting > New Rule
Select "Use a formula to determine which cells to ...

0

If you are using Excel 2007 or newer, this should get you going in the right direction:
Add a new column to your worksheet based on your MIN calc (let's say
the value is set to 1 if it is the required MIN
Highlight the entire column On the Home Tab, select Conditional Formatting >> Highlight Cells Rules >> Equal To...
Enter a 1 for the value (or whatever ...

1

If your data is in column A (XGTI,BIOC,...) in column B (10,12...) copy column A to another column E for example and remove duplicates(Data Tab, Remove Duplicates) you will have one XGTI, one BIOC... in F1 write the following:
=SUMIF($A$1:$A$10,E1,$B$1:$B$10)
Where A1:A10 is the column of XGTI,... and $ for absolute reference to let you drag the formula.
...

1

Here is another approach:
Right-click the summary sheet
select Move or Copy
click "Create a copy"
Select "New Book" in the "To book" drop-down and hit OK.
At this point the formulas will still be there.
Select all with Ctrl-A and use Ctrl-C to copy
Open the Paste Special dialog and paste as Values. There are half a dozen different approached to "Paste ...

0

The comments are correct (prasanna); here is what they are saying in more detail:
Make a new blank sheet. Return to sheet you want to copy. Hit CTRL-A to select all, then CTRL-C to copy. Go to the new sheet, CTRL-V to paste, immediately followed by CTRL (by itself), followed by E (again by itself).
Those are the shortcut keys and sequence to do a 'Paste ...

1

It really depends on the change you want to make to a formula. If you want to change the value of a constant, like =B2+5 to =B2+7, and the constant needs to be changed quite often, consider using a named formula instead of a constant.
Open the Name Manager and create a name that describes the nature of your constant, for example "Adjustment" and let it ...

2

To get the count in N1 put:
=COUNTIF($A:$A,"*" & M1 & "*")
then you can use this formula to find the value with the most:
=INDEX(M:M,MATCH(MAX(N:N),N:N,0))
In one formula, with your prefixes still in M1:M3, use this array formula:
...

1

In Excel 2016, instead of “Customize Ribbon”, you will need to go to the “View” pane in Excel Preferences, then check the box next to “Developer Tab”.
Excel 2016 for Mac - View Preferences Pane

0

For a formula we need a title row. The formula needs to start in the second row. So in B2 put:
=IFERROR(INDEX($A$2:$A$9,MATCH(1,INDEX(($A$2:$A$9<>"")*(COUNTIF($B$1:B1,$A$2:$A$9)=0),),0)),"")
This is an array formula. The calculations are exponential and too many of them or too much data will slow the calculations and sometimes stop excel. This ...

2

To get the count in N1 put:
=COUNTIF($A:$A,M1)
then you can use this formula to find the value with the most:
=INDEX(M:M,MATCH(MAX(N:N),N:N,0))
To return the value that is repeated the most with one formula, use this array formula:
=INDEX($A$1:$A$4,MATCH(MAX(COUNTIF($A$1:$A$4,$A$1:$A$4)),COUNTIF($A$1:$A$4,$A$1:$A$4),0))
Being an array it needs ...

0

How about this:
=INT((<next_payday>-<final_date>)/14)
with your example:
=INT(("13/12/16"-"3/6/16")/14)
the /14 at the end represents the frequency with which you get paid (every 14 days)

0

Non of the answers worked until I realized that there are SPACEs in the cells. Not the space that you can type however. So what I did was copy the SPACE in the cell, and use Find/Replace to replace all of them into empty. Then it worked!

0

You can't without more information.
For example, if the two dates are:
5/11/2016
5/18/2016
There is a single Friday between these two dates. If you get paid every other Friday, how could anyone tell if that Friday (5/13/2016) is a payday or not ???

0

If your string of digits will always be the first set of digits in your string, then you can use the following formula. It is an array formula entered by holding down ctrl+shift while hitting enter:
=MAX(IFERROR(--MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),{7,8,9,10}),0))
If there might be shorter or longer substrings of digits prior to ...

2

This should work in office 2010, I have no way of checking; I checked it in office 2016.
It's an array function, which is indicated by the braces around it. You have to select the cell you want to put the function in, paste the function into the fx area at the top of the screen and press CtrlShiftEntr rather than Enter in order to indicate to Excel its an ...

1

Here's the equation:
=IF(WEEKDAY(B2)=B1, INT((DAYS(B3,B2)/B4)-1), INT(DAYS(B3,B2)/B4))
Description:
The day of the week you get paid needs to be put in cell B1
encoded as Sunday=1, Monday=2, .. ,Saturday=7.
The cell B2 should have the starting date.
The cell B3 should have the end date.
How often you get paid in days needs to go cell B4 (i.e. once
...

0

Had a similar problem just now, printers weren't showing up on Word and Adobe, just go to Device Manager, Right Click and Properties on the printer, go to the Settings tab and click "Devices and Printers folder, after right click any printer and select troubleshooting, this will restart the service for the printers and will fix any frozen USB port, worked ...

0

There are functions like WEEKNUM() and WEEKDAY(), and you can just subtract dates form each other to get number of days in between.
Your request is not clear enough for me to give a formula, but for example TODAY()-<somecell> gives you the number of days between them, and WEEKNUM(TODAY())-WEEKNUM(<somecell>) gives the number of weeks between ...

3

This requires that the results start in at least the second row, only because I need a row above for the COUNT() function.
Also it is an array formula:
...

2

Can you add an aid column, which modify the number values of column A?
If you use the formula
=TEXT(A1,"'0")
then your slightly modified second formula will work
=LOOKUP(1,0/FREQUENCY(ROWS($1:4),COUNTIF($B$1:$B$10,">="&$B$1:$B$10)),$A$1:$A$10)

0

This assumes that:
your translate table is in columns A & B
your data entry column is column C
the data entry column as been formatted as "00" (to allow leading zeros to be entered)
Place the following Event Macro in the worksheet code area:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim C As Range, A As Range, B As Range
Dim t As ...

0

So what you are really looking for is conditional formatting (Or so it seems)
Here's a snippet from Microsoft of how that happens. See below for the example or for the full example see the link below.
https://support.office.com/en-us/article/Use-a-formula-to-apply-conditional-formatting-fed60dfa-1d3f-4e13-9ecb-f1951ff89d7f
BlockquoteCreate ...

0

You could use this formula:
=SUMPRODUCT(($E$3:$E$8)*(LOOKUP($B$3:$B$8,$H$3:$H$8,$I$3:$I$8)))
The order in the territories and companies do not matter, but the lookup range must be in ascending order in the lookup column in this instance Column H needs to be in ascending order.

4

Use the array formula:
=INDEX(A1:E1,MATCH(MIN(IF(A2:E2>0.8,A2:E2)),A2:E2))
Array formulas must be entered with Ctrl + Shift + Enter rather than just the Enter key.

9

You want to use the IFERROR function:
=IFERROR(VLOOKUP($P5,GW30!$CI:$CL,2,FALSE),"")
If there's no error, it will return the value as normal. If there is, it will return what's after the comma, in this case an empty string.

5

You can wrap your formula with the iferror condition and set the default value for the error condition to be blank e.g.
iferror(VLOOKUP($P5,GW30!$CI:$CL,2,FALSE),"")

0

[...] is there a way to do that without using a mouse?
Yes, there is.
Keyboard shortcut
In Excel 2013 select the range like this:
And then press Alt, H, F, I, S, Enter.
Explanation
Press Alt to activate the accelerator keys,
followed by H to select the Home ribbon,
followed by F,I to select the Fill button
followed by S to select the Series menu ...

0

In Z2 enter the Array Formula:
=(-INDEX(U2:Y2,MATCH(TRUE,U2:Y2<>0,0))+LOOKUP(2,1/(U2:Y2>0),U2:Y2))/INDEX(U2:Y2,MATCH(TRUE,U2:Y2<>0,0))
and copy down:
Array formulas must be entered with Ctrl + Shift + Enter rather than just the Enter key.
You can use a MUCH simpler formula if the %-change is never negative!

2

Assuming that B1 is cell X and B2 is cell Y, this should work:
=IF(AND((B1/B2)>=-0.3,(B1/B2)<=0),0,IF((B1/B2)<-0.3,1/((B1/B2)^2),(B1/B2)))

1

One of them shows double quotes in fields and the other does not
Does anyone know why this is the case?
The first file has spaces following the commas.
Remove the spaces to get the same behaviour with both files.
According to RFC 4180, spaces outside quotes in a field are not
allowed; however, the RFC also says that "Spaces are considered part
...

1

You can insert a new column G in the table for Rates and write the corresponding rate in it (at the same row of store A write 0.45, B write 0.37 ...)
The result will be :
=SUMPRODUCT((A3:A8="New York")*E3:E8*G3:G8)
Sumproduct will multiply each cell in column E (Q2) by the rate in G if the Territory (column A) is "New York"

1

As the OP commented above, removing the spaces solves the problem but if you don't want to manipulate the file before import, simply mark both Comma and Space as delimiters and check the "Treat consecutive delimiters as one" checkbox in the Text Import Wizard.

1

Today I came across this link RDBMerge, Excel Merge Add-in for Excel for Windows, which I think will serve the purpose.That is a free macro based tool.

0

The paste forumula method works when one is copying and pasting into the same workbook. When copy to a different workbook, choose paste special and then choose CSV format.

0

Shortcut to Fill-down (or fill-Right):
First select Select what to fill down(or right) then
Option 1: To fill-down all the cells(till end of excel sheet): ( Shift + ( End Then ↓ ) ) Then ( Ctrl + D )
To fill-Right: ( Shift + ( End Then → ) ) Then ( Ctrl + D )
Option 2: To fill-down: ( Shift + ( PgDown^N Then ↓ ) ) Then ( Ctrl + D )
N= number of times you ...

2

Consider:
=SUMPRODUCT((Y1:Y7)*(COUNTIF(Z1:Z7,X1:X7)>0))
your sample data:
This requires neither a "helper" column nor an array formula.

1

Yes. This is due to how windows works, and is not just something Microsoft excel has.
Whenever you have an open dialog, you can paste a URL into the filename box and hit Open. Explorer will then download the file to a temporary location and use that location to open the file.
This is similar as to how files are opened when they're attached in outlook. The ...

0

You can use the following formula for each row, starting in the first data cell (assuming you three columns start in cell A1):
=IF(COUNTIF(C:C,A2)=0,0,B2)
This will return the number of column y for all rows that have the value of column x appear somewhere in column z.
Make sure to adjust the first parameter to COUNTIF if you do not want the column ...

0

I'd add a new column with a formula:
=IF(ISNA(MATCH(Z2,X:X,)),0,INDEX(Y:Y,MATCH(Z2,X:X,)))
This column can then be totalled as desired.
The MATCH method can find the item you're looking for in the other range, the INDEX method looks up the equivalent value, and we turn it into 0 if the MATCH fails (i.e. the result is #N/A)

2

Assuming the original table is in columns A and B, pick any cell and enter:
=OFFSET($B$1,COLUMNS($A:A)-1+(ROWS($1:1)-1)*2,0)
copy this cell across and down:
Note:
Using the right formulas, you can always map any single column or row into a 2-D table and any 2-D table into a single column.

2

Try this:
=MAX(IF(D2=$A$2:$A$15,$B$2:$B$15))-MIN(IF(D2=$A$2:$A$15,$B$2:$B$15))
This is an array formula and must be confirmed with Ctrl-Shift-Enter.

2

Use the Aggregate Function:
=AGGREGATE(14,6,$B$2:$B$18/($A$2:$A$18=D2),1)-AGGREGATE(15,6,$B$2:$B$18/($A$2:$A$18=D2),1)
The first finds the largest number the second the smallest where the Vehicle number matches that in column D

1

Could you use this cell in B2
=min(F3+sum(B5:B14),300)
Will sum the values you mentioned but be capped at 300.

0

This runs fine on my computer (Windows Excel 2013)
Sub test()
a = "C:\Users\" & Environ$("UserName") & _
"\Documents\Workout Logs\" & _
Format$(Date, "mmmm-yyyy") & _
"\" & _
Format$(Date, "mmmm-dd") & Format$(Time, "hh-mm") & ".xls"
MsgBox (a)
End Sub
Msgbox results C:\Users\username\Documents\Workout ...

0

If you can live with sorting in choices, then this appears to work:
h/t: http://www.exceltactics.com/combine-data-multiple-rows-one-cell/5/

0

We build a list of the column A uniques and put it in column E and then perform a set of loops to count the uniques in the other columns:
Sub Macro1()
Dim N As Long, i As Long
Dim c As Collection, v As Variant
Dim M As Long, j As Long, rc As Long
Dim K As Long
Columns("A:A").Copy Columns("E:E")
Range("E:E").RemoveDuplicates ...

0

Use this formula:
=MOD((SUMPRODUCT(1/(COUNTIF($A$2:$A$13,$A$2:$A$13)))-SUMPRODUCT(1/(COUNTIF($A2:$A$13,$A2:$A$13)))+1),2)=0
Make sure to pay attention to what is and what is not relative reference. This will not work if there are blank spaces.

1

You would need a helper column. Put in that column the formula =IF(<name>=<name in prev line>,1,-1)*<value from previous line> (and a hard 1 in the first row). This will result in the sequence 1,1,1,-1,-1,-1,-1,1,1,... in the helper column, switching from 1 to -1 or back right whenever the name changes.
The conditional formula is then ...

2

You need to insert a helper column, where you check number of blank cells.
For example, if you need to check columns A - F, and G is an empty column, then enter in G1:
=COUNTA(A1:F1)
Now you have just to sort by column G, and delete rows with 0.

Top 50 recent answers are included