## Hot answers tagged microsoft-excel

3

The SUM function takes a list of numbers or references as arguments. When you separate ranges with a comma, you are sending two arguments (references) to the SUM function.
The INDIRECT function accepts one ref_text argument (and the optional a1 argument) and "returns the reference specified by a text string."
SUM function, Syntax:
SUM(number1,[...

3

You can add a column with the Today function date, then compare TODAY cell with Invoice received date+9 cell and if TODAY is smaller then Invoice received date cell turn orange
You have to set another IF condition to check "Y" value in the first column
So you can set
=and((B2+9)<=TODAY(),B1<>"Y")
In the picture below you can see where to find ...

3

With data in columns A and B like:
In C2 enter the array formula:
=IF(ISERROR(MATCH(A2:A11,B2:B11,0)),A2:A11,"")
and copy down. Then in D2 enter the array formula:
=IF(ROW()-ROW(D$2:D$11)+1>ROWS(C$2:C$11)-COUNTBLANK(C$2:C$11),"",INDIRECT(ADDRESS(SMALL((IF(C$2:C$11<>"",ROW(C$2:C$11),ROW()+ROWS(C$2:C$11))),ROW()-ROW(D$2:D$11)+1),COLUMN(C$2:C$...

2

If you know that you might want to exclude certain items from the formula, you can prepare for this as follows:
in R1 through R10 enter 1
use the formula: =SUMPRODUCT((R1:R10)*(Q1:Q10))
Now if you want to exclude Q7 and Q8, just set R7 and R8 to zero.

2

You can't omit these cells from being included in the formula without making changes in the formula.
You can't have your cake and eat it.
I suggest you change the formula to exclude the cell explicitly, like
SUM(Q1:Q10)-sum(q7,q8)
and
SUM(E6:N13)-SUM(P6:AJ13)-sum(q7,q8)

2

At first create a new column with this formula:
=LEN(A1)-LEN(SUBSTITUTE(A1,"0",""))
With this formular you can find out how many zero characters are in the String.
In the second step you have to filter your table:
Click on Start
Click on Filter
Filter the new column to show only lines with a specific number of characters

2

I'll assume you're using a more recent version of Excel. To make a line graph of your data in the way you described, copy the data columns for MPH and RPM side-by-side in that order. Then select both columns. Click 'Insert' > 'line graph'. To add a trend line and formula for your data, right click on the newly made graph and click 'add trendline' in the ...

2

I'm not aware of a way too "view" the documents without actually viewing them, unless you found a software too do so such as Link Checker (https://www.ablebits.com/word-links-checker/index.php).
Good luck, though

2

Microsoft Excel has a plugin called PowerQuery.
PowerQuery allows you to import data from your sheet, and run an 'unpivot' operation.
This is a very handy tool. It will take you a bit of time to set it up and get it working the first time, but once you do it will be useful for years and let you do this operation very quickly.
Here are some details on how ...

1

Select the range to be formatted. Assuming B2 was the cell you first clicked when making this selection, go to "Conditional formatting" -> "Use a formula..." and enter =AND(B2<=TODAY()-9, A2<>"Y").

1

This formula will convert it to a time. I made it as dynamic as I could:
=TIME(IFERROR(MID(A1,SEARCH("h",A1)-MIN(SEARCH("h",A1),3)+1,MIN(SEARCH("h",A1),3)-1),0),IFERROR(MID(A1,SEARCH("m",A1)-MIN(SEARCH("m",A1),3)+1,MIN(SEARCH("m",A1),3)-1),0),IFERROR(MID(A1,SEARCH("s",A1)-MIN(SEARCH("s",A1),3)+1,MIN(SEARCH("s",A1),3)-1),0))

1

Copy a cell containing your additive (1), select the relevant range and Paste Special Add.

1

Click on any of the graphs; in the formula bar you will see the 'Formula' that creates this graph, for example =SERIES(Sheet1!$A$3;Sheet1!$B$2:$N$2;Sheet1!$B$3:$N$3;1)
The first parameter is the Title of each graph;
the second parameter is the X-range
the third parameter is the Y-range
the fourth parameter is the sequential count.
Your problem is the ...

1

### Is there a way to make a single bar chart that tracks color based on date in Google Sheets or Excel?

First you need to calculate date instead of week number, I've used this formula:
=DATE(2016,1,1)+(B2-1)*7,
you may have other definition for it, but I think it fits for purpose,
after inserting it, also change it's format: home - number format - custom: mmm
next, insert a pivot chart (in Excel 2013: insert - pivot chart)
Select columns:
legend (series): ...

1

You could use IF functions as follows:
=IF(P2<>"",P2&", ","") & IF(Q2<>"",Q2&", ","") & IF(R2<>"",R2&", ","") & IF(AD2<>"",AD2&", ","") & IF(AE2<>"",AE2&", ","") & IF(AF2<>"",AF2,"")
This has a possibility of having an extra comma at the end, which you can remove using the ...

1

Option 1: First, adds helper columns for each possible mail address column, use IF formula to get comma appended to each non-empty string, otherwise empty string is returned. For example, formula of helper column for P2 is =IF(P2<>"", P2&",", ""). Then concatenates above helper column together.
Option 2: Concatenates all your string as you did ...

1

To solve one mystery: Excel places single quotes around the sheet name if the sheet name contains spaces.
=INDEX(apestatic!$E$42:$L$2140,MATCH('Graphed data'!$E4,apestatic!$E$42:$E$2140,0),MATCH(H$3,apestatic!$E$42:$L$42,0))
I adjusted the formula to use an absolute reference for the range in the last Match. This will enable you to copy the formula down ...

1

Your values are text rather than true date/times. Once you converted the cells to date/times, you should see:
with the proper formatting for cell A3

1

I've seen this occur when you have Filters turned on. If you turn off the Data Filters, the "My data has headers" option should be available.

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

1

Since you are using the SUM() function, there is one other option. SUM() ignores text values, so you could enter the values in Q7 and Q8 as text. Format the cells as Text and enter the numbers with a leading single quote character. In the screenshot below, Excel shows the green warning triangle for numbers stored as text, but the SUM() function only ...

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:

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