Spreadsheets

A spreadsheet is an interactive computer application program for organization, analysis and storage of data in tabular form. Spreadsheets developed as computerized simulations of paper accounting worksheets. The program operates on data represented as cells of an array, organized in rows and columns. Each cell of the array may contain either numeric or text data, or the results of formulas that automatically calculate and display a value based on the contents of other cells.

Share tech news, updates, or what's on your mind.

Sign up to Post

I have an Excel table with columns A-H, rows 2-100. It is filled with text data (Application Names). I'm looking to populate column H with a list of common applications that are present in all 6 columns of the table.

Duplicate value formatting doesn't work as it identifies anything that appears more than once, where as I'm looking only for what is present in all 6 columns.
0
Free Tool: SSL Checker
LVL 9
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Dear Experts:

I got the following file paths entered in column A

M:\KLS_General\DMD\PM_1\MyFile_Final_2.pdf
M:\KLS_General_17\DMD\PM_2_3\MyFile2.pdf
M:\KLS_General_2\DMD\PM_4_5_6\MyFile6_Final_23072017.pdf

Is it possible to extract the substrings after the fourth (4th) back slash using a formula. I got a macro for this purpose but I wonder whether the same could be achieved using a formula, ie. applying the formula the results would be as follows:

MyFile_Final_2.pdf
MyFile2.pdf
MyFile6_Final_23072017.pdf

Help is much appreciated.

Thank you very much in advance.

Regards, Andreas
0
Hi,
There is a table  - its called “sub-productLevel”  - it has sales data for the combination of customer + subproduct + month

Now there is a table called productLevel – which rolls up all the subproduct underneath it.

I did create pivots on both those data. Sometimes there is a data discrepancy between the product level and sub-product level.
How do I create a pivot:
1.      which is based of the subProductLevel tab, but also references the productLevel tab.
2.      If there is a difference between the subProductLevel and productLevel, it shows it by the name “xdiff”
3.      So that finally when the diff is added up, the subProductLevel data total match exactly to the productLevel data

so explain the above, I just added a dummy xdiff row to the subProductLevel data to make the pivot show what I needed here...

Thanks
pvtSol.xlsx
0
I am trying to see how many there visits are per day for each visit, so that when I put it in Minitab I can see if there is a correlation between number of visits and another criteria.  I currently have the following formula and the following data layout.

A                         B                                 C                                    D
Provider              Date/Time In             Date/Time Out     Number of Visits
Richardson      6/21/17 13:50      6/21/17 14:20
Richardson      6/21/17 14:20      6/21/17 14:41
Richardson      6/21/17 15:20      6/21/17 15:42
Richardson      6/27/17 10:17      6/27/17 10:58

=SUMPRODUCT((DAY($B1:$B151)=DAY(B2))*(MONTH($B$1:$B$151)=MONTH(B2))*(YEAR($B$1:$B$151)=(YEAR(B2))))
0
I need a formula to give the average of all the cells in a row with numbers but excluding the cells with 0's in them.
0
I've done this in Excel with a bit of manipulating, and I'm hoping there's a way to reproduce it in Google Sheets.

I am tracking seasons stats for a sport.  On the first sheet, I list the season totals.
In the same format, I list the match totals on separate sheets, labeled accordingly.
I want to have the season totals sheet calculate the sum of those specific stats for every other sheet.
I don't plan on having any sheets that won't be setup this way, so if there's a possibility to have the Season Totals sheet show a sum of (example) Sheet2!A4 + Sheet3!A4, etc... that should work.
0
Hello,

Is there an Excel formula (or formulas) which will merge data from multiple worksheets into a single summary worksheet and keep the combined rows sorted?

For example, suppose you have three checking accounts, each with identical column headings and each occupying a separate worksheet  as shown here:

2017-08-03a_EE.png
2017-08-03b_EE.png
2017-08-03c_EE.png
Note that in this screenshot:

• each colored worksheet tab is named named by the last 4 digits of its account number
• the account for each transaction is identified by the same 4 digit code in column B
• the transactions in each account are sorted by date

I'm looking for an Excel formula that will capture the transactions from all three accounts and combine them into a single summary worksheet as follows:

2017-08-03d_EE.png
In this screenshot, note that:

• each transaction is still labeled by its respective account number in column B
• the transactions are in chronological order
• the balance shown in column G is a combination of all three accounts

I'm hoping there is a solution which will populate the Combined worksheet automatically and which involves only Excel formulas since that will make them tweak-able for me. The 2nd option is to use VBA but then I will be dependent on EE for changes. I prefer not to use an Excel add-on since I've found them to usually be un-tweak-able.

Thanks a bunch

File attached:

Combined-Accts_EE.xlsm
1
HOw to sum Across Multiple Sheets as per criteria ??

Where D9:P9 are sheet names, C3:C87 is the criteria range, H3:H87 is the sum range
0
Hi,
There is a big list of people with all their demographics.
There is a sheet called “Prefferred_Names” – which currently has two names

Using sumproduct or similar, I would like to do a partial search for all the strings mentioned in sheet “preferred names” against the column A of sheet1.
partial-Search.xlsx
0
I need to add a summary column in a spreadsheet called "Linked", summing minutes from multiple columns with the following criteria:
If [WO_Change] column does not contain text string "Cancelled" or "Deferred", then in [Total_Minutes] column, sum minutes from [Minutes], [Minutes2] and [Minutes3]: otherwise, [Total_Minutes] column to be blank.

In attempting to solve this I came up with the following formula in the [Total_Minutes] column, but got some whacky results:

=SUM(SUMIF([WO_Change],{"<>*Cancelled*","<>*Deferred*"},([@Minutes]:[@Minutes3])))

I  have attached a sample spreadsheet with the "Linked" tab as outlined above.

Thanks,
Andrea
Sum_if_or_EE.xlsx
0
Enroll in August's Course of the Month
LVL 9
Enroll in August's Course of the Month

August's CompTIA IT Fundamentals course includes 19 hours of basic computer principle modules and prepares you for the certification exam. It's free for Premium Members, Team Accounts, and Qualified Experts!

I have an Excel spreadsheet called 'WO_Tracking'. There is another spreadsheet, called 'Linked', that is linked to 'WO_Tracking', and is formatted as an Excel table. There is a third spreadsheet called 'Summary' to serve as a dashboard for supervisors.

In the Summary spreadsheet, in cell A2, I am trying to get the median (excluding cells with zero values) of Column F (Total Minutes) in the 'Linked' spreadsheets, using the following formula:
=MEDIAN(Table2[Total_Minutes])

But I am only getting '0' as a result.

I have provided a sample workbook with all three spreadsheets included. The formula needs to be based on the 'Linked' spreadsheet, not the 'WO_Tracking' spreadsheet.

Thanks!
Andrea
Linked_Spreadsheet_Median_Formula_E.xlsx
0
I have an Excel spreadsheet called 'WO_Tracking'. There is another spreadsheet, called 'Linked', that is linked to 'WO_Tracking', and is formatted as an Excel table. There is a third spreadsheet called 'Summary' to serve as a dashboard for supervisors.

In the Summary spreadsheet, I am trying to total the following from the 'Linked' spreadsheet:
Linked![Minutes] column + Linked![Minutes2] column, excluding minutes in rows that have either 'Cancelled' or 'Deferred' text string in [WO_Change] column

I have provided a sample workbook with all three spreadsheets included, and filled in the expected result in red in the Summary tab. The formula needs to be based on the 'Linked' spreadsheet, not the 'WO_Tracking' spreadsheet.

Thanks!
Andrea
Linked_Spreadsheet_Table_Formulas_E.xlsx
0
A student is giving the test in 12 rounds and every round has 4 subjects. I want to make sheet where I can show student result of all rounds on choose subject. For example, if I choose English subject then the formula should show a result of every round which he was taken .
Sample is attached
Book1-V2.xlsx
0
Hi,

I have attached a spreadsheet with some code and functions, but I want to know if I can achieve the following.

Spreadsheet has 7 tabs.  The tabs I am interested in is tabs 2 - 7

Each tab has one row of data.  Now if you go to tab 2 - Risks, the go to column J and in the dropbox click Action.  You will now see that the row has gone.

Go to the Action tab, and you will see that the row appears here, and that is correct.  I column B you will see an R.  As it was moved from the Risk tab, it was enetered as a R for Risk.  But as it now resideds in Action, I want the cell automatically update to an A.

This movement and changes will only aplly for rows that move between Tabs 2-4,  Risk, Action, Isse and Dependancy.

Please let me know if any more info is needed.
Sample-Project-Raid-Log-0.1a.xlsm
0
I am attaching a file that has two Start entries (C4 and C31). This is just a sample set so this would be repeated for thousands of rows. I want to know how to do two things: how do I count the number of entries between each Start e.g. it should be 26 - but to do that for all the rows in the data set. Secondly how do I show the time elapsed (in hours) between each occurrence of start. (cell T7) - again for each occurrence  of this start...basically the idea is that a pump starts...does its thing. Stops and then starts again...
EE--calculating-rows-between-entrie.xlsx
0
I have an Excel 2013 spreadsheet with over 3,000 entries.  The spreadsheet has customer numbers and Company Names (see attached sample) - my original spreadsheet has many more columns but I tried to simplify for explanation purposes. On the sample I have attached there are five customer numbers (3, 4, 5, 6, 7) and only two company names.  I am trying to find a quick way to determine what customer numbers are listed with both companies.  So in my example, customer #4, #5 and #7 are the customers that are associated with both companies.  That is what I am trying to extract.  I don't need to know the customer numbers that are only associated with one company. I need to pull only the customer numbers that are listed for both companies.  Any help is appreciated.  Thank you.
0
How do i write a nested if statement for the following?

If greater than  14 but less than 16 return £10000
BUT If greater than  16 but less than 18 return £20000
AND if greater than 18 return £30000

I tried =IF(AND(C9>=14,C9<16),10000,0)
Which returns £10000 however when i try nest it, everything just returns #VALUE?


Thank you
0
I am trying to check a cell in spreadsheet and if it is blank then make a range of cells blank.   It is importatnt that I have to do it in the cell and cannot use VBA
So If I have Cell M2 then evaluate cell named RETURN_ID_1 ="" then cell the Range of cells E2:H2 to = 1
=IF(RETURN_ID_1 = "",RANGE(E2:H2)  = 1,)

Right now what happens is I get #name in cell M2 and the range stays the same.
0
I am trying to modify some formulas that I have used in the past and understand the difference between the two.

I have a spreadsheet with the TABs  'Orders' and 'KOB1'

I want to use the two formulas (so I can compare and understand) to pull information from 'Orders' to 'KOB1' using column A on both sheets.

I want to match columns 'A' on both sheets and then pull column 'C' from 'Orders' to fill columns 'B' - using the index formula and 'C' - using the Vlookup formula on KOB1.
See attachment
test.xlsx
0
Free Tool: Site Down Detector
LVL 9
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

I have a number/text in a spreadsheet that I am using to reference a row for vlookup.  I want to reference a number in another row without having to do another vlookup.  Can I use a formula to add one to the row reference that is 2017-29 to make it 2017-30.  I can do it manually with the pull handle, but cannot find a formula that will work.
0
I have a Summary Excel Table in a spreadsheet that is linked to a production tracking spreadsheet that needs to update in real-time as data in entered the production tracking spreadsheet.

Description of the Summary Excel Table spreadsheet:
Sheet 1:
NOTE: Sheet1 has a number of columns that are not relevant for this specific question, but I left them in to replicate the spreadsheet I will be using to implement this solution.
Col A is a list of WO#s pulled from the production tracking spreadsheet
Column headings labelled Date1, Date2, etc. through to Date12 = date that process or rework minutes were entered into the spreadsheet. Note that the minutes associated with the date entry are always in the column to the immediate right of the date column (Col D minutes (whether process or rework minutes) are for Col C date entries, etc.).
Column headings labelled Min1, Min2, Min3, Min4, Min5, Min6, Min7 = process minutes
Columnn headings labelled RWK_Min1, RWK_Min2, etc. .through to RWK_Min6 = rework minutes

Sheet2: I would like to be able to...
Sum the number of process minutes (in one column) and rework minutes (in another column) by work order # between specific date ranges listed in Sheet2, Rows 2 and 3.

I have included a sample spreadsheet as described above, containing Sheet1 and Sheet2, with a description on Sheet2 of the solutions I am hoping for.

Lastly, can you let me know if the formulas required for this solution will be a …
0
Hello All,

The strangest thing started happening this morning at our office. We can open spreadsheets just fine. We can be working in excel just fine, but then we will go to open a spreadsheet one minute later and it will hang at 100%. At that point, any spreadsheet we try to open freezes at 100%. After a few minutes, it will open and work fine. However, if we exit out of the 100% loaded window, it leaves the temp file on the desktop. When we go to open the spreadsheet again, it says it is locked by the user. It will eventually go away after a popup comes up saying "could not quit excel."

Things to note:

This is happening on all LOCAL spreadsheets. These are not networked, they are saved right to the desktop of a non-AD computer. We are running office 16 on windows 10 and windows 7 computers. This is the click to run version of  office. All updates have been done to office and the computers themselves.

Things we have tried:

Complete un-install and reinstall of office 365 (registry, temp files, even microsoft's tool to remove office)

Deleting all printers

Setting Microsoft XPS printer as default

Disabling all add-ons (we dont run any, but we checked anyway)

OffCat is showing no errors.

Any ideas?
2.PNG
1.PNG
0
I created a spreadsheet and would like to add color but do not know how to ask the right question in Google.  Here is what I would like to happen:

I have a formula in a cell: =IF(ISBLANK(C4),"",DATEDIF(C4,TODAY(),"d"))

I would like to add color as such:  Green if it is under the maximum days allowed (in this case it would be 365) and Red if it is over the maximum days allowed while keeping the formula already in place intact.  Any assistance would be greatly appreciated.  Thank you.

Allen
0
I have a calculated value in cell E2,

In cell F2, I want to do the following:
1. display the same color displayed in E2
2. if value displaying in E2 < 20, display ADVISORY in F2
3. if value displaying in E2 > 19, display RFC in F2
4. if value displaying in E2 > 39, display ESCALATE in F2

Thank you for your help!
JohnD
test.xlsm
0
I'm getting a "Too Few Arguments" error with the following formula:

IF(ISBLANK(C2), DATEDIF(B2,TODAY()), DATEDIF(C2,B2),"d")

Basically, B2 will always have a date in it, C2 will not.  I want to calculate the number of days and use TODAY if C2 is blank, otherwise take the two dates and calculate the difference.

But what's wrong here?
0

Spreadsheets

A spreadsheet is an interactive computer application program for organization, analysis and storage of data in tabular form. Spreadsheets developed as computerized simulations of paper accounting worksheets. The program operates on data represented as cells of an array, organized in rows and columns. Each cell of the array may contain either numeric or text data, or the results of formulas that automatically calculate and display a value based on the contents of other cells.