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

In the given sample in sheet-1 there is a column called skill where filter is applied.
What I need to do is apply filter for each skillset and then count the frequency of numbers under the column baseline level and then place the frequency of each number into sheet-2 under L1,L2,L3 of the current level.
for eg-
in sheet 1 filter the skills column by lets say, the skill "Angular JS".
Therefore, now the baseline level column contains only entries i.e 0 and 1. So count the occurences of 0s and then in Sheet-2 look for the row with the entry Angular JS. After that fill L1 (current level) with 1 (as 0 only occurred once) and L2 with 1(as 1 has occurred only once).
finaltask.xlsx
0
Cloud Class® Course: Ruby Fundamentals
LVL 12
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

Hello
I like to know more about excel sheet.
I have microsoft excel sheet with macro function and now i need to move this excel into google but by macro function not working on that.
Can you please help me to solve this ?
0
I use an Excel spreadsheet to calculate time for payroll.  I would like to be able to use a sumif statement to calculate holiday, vacation, and sick time based on cell color.  How can I do that?  I have attached a copy of the spreadsheet I use.
0
I have a large cash forecasting model with weekly data in each column.  Each column has numerous index/match formulas that look up values from other sheets in the workbook.  Each column concludes with ending cash and the next column begins with with that ending cash from the previous week.  

For example, B1 = A20, C1 = B20 and this format continues outward for 75 weeks or until the end of 2019.  So, there are literally thousands of index/match formulas and a few hundred circular references in this model.

Everything works fine until you reach ~30 columns and then the numbers start to drift.  For example, assume AC20 = 100, therefore AD1 should = 100 also.  But it doesn't, It displays 98.  However, if you press F2 and then F9 the answer will be 100.  It's the strangest phenomenon I've ever seen in Excel.  

Oddly the error pattern seems to run in a sine wave fashion.  The first column will be off -2 as in then example above and then the next column is off +4 and then -8 and then +16, etc.  This same pattern continues through the last column and the variances get very large.

I have tried numerous combinations of formula iteration tweaks in Excel Options, I have tried "precision as displayed" and I have tried rounding the formulas in the target cell and the destination cells of the index/match formulas.  Nothing seems to make a difference.

Does anyone have any idea what could be causing this error to occur in Excel?  I've never see anything like this before.
0
I have a column of dates, of which I need 2 formulas ... one for the maximum consecutive days listed, and the other for the current consecutive days.

Example spreadsheet attached. Thanks!
Consecutive.xlsx
0
How can I see total sales by month in a Google Sheets Pivot Table?

Here is the source data:
0
what is the formulae or method to return as no change,  if a key phrases,  for e.g.   Senior Engineer  exists in both strings

Senior Engineer (ABC)  =   Senior Engineer,  Enterprise Infra Branch

using search(A2, B2),  some how don't recognised after the phrases with space, special characters etc.

Thank you !
0
For reference here's the sheet:

https://docs.google.com/spreadsheets/d/1CS-fYhqeKRmEiJC5XA6kRIwIjyBDlBcXj-K9FHjCZjA/edit?usp=sharing

I am trying to create a filter that shows me all the data for May based on type and course category. I highlighted these in blue in the sheet.

I worked out how to count the type and category using this formula:

=arrayformula(SUM((Resolutions!D:D="Complaints")*(Resolutions!J:J="Training and Assessment")))

but I would like to further count by month in the future.

Can you please help?
0


How to add the texture in rows?
0
I'm posting this as a solution for the record and the benefit of others, as it's a satisfactory workaround to a common problem using array formulae in google sheets.

Often with spreadsheets you may need to fill down the same value into blanks below until a new value is found, see screenshot attached.

screenshot
Easy with regular formulae, a little more tricky with arrays, but possible, see formula also in screenshot.

If anyone has a better way please post here. I'll leave this open for a short while in case anything comes up.

Some google sheet formulae don't currently work with arrays (eg INDEX), but both MATCH and VLOOKUP do, so this workaround is possible.
0
Keep up with what's happening at Experts Exchange!
LVL 12
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

i want to import data (dynamic) to Google sheet from excel online (onedrive). i know it can be done from other datasources but unable to pull to google sheet
0
Hello and thanks in advance for any help you can give me.

I have written a simple SSIS package in Visual Studio 8 that reads from MSSQL and outputs to Excel 2003.  The package has two dataflows each writing to a different worksheet in the same workbook.  The first dataflow works fine but the second fails with the following error:

[OLE DB Destination [344]] Error: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.
[OLE DB Destination [344]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.  The "input "OLE DB Destination Input" (357)" failed because error code 0xC020907B occurred, and the error row disposition on "input "OLE DB Destination Input" (357)" specifies failure on error. An error occurred on the specified object of the specified component.  There may be error messages posted before this with more information about the failure.
[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED.  The ProcessInput method on component "OLE DB Destination" (344) failed with error code 0xC0209029 while processing input "OLE DB Destination Input" (357). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.  There may be error messages posted before this with more information about the failure.

During development both flows worked, but now I cannot get the second to work at all.  It …
0
How to round to 0, 5 or 9 which ever is closest?
0
i need a newest to oldest order i google spreadsheet.
0
Hi All

I'm trying to build a macro where it will prompt the user to select a csv file for import. I have looked around the net and found several macros however they won't prompt the user to go to the file for importation but rather have the spreadsheet and csv file within the same folder to allow for the import to work.

What I'm looking for is an Excel macro where it can update a tab called Commission and columns D11:O427. I'm wondering when the csv file is imported can it be formatted in the same way the data is presented, see Capture for an example.

Thanks all!
Capture.PNG
0
i am trying to create the below.

Jan 18
Authorised      13
Pending               8


when i enter the letter A or P i another sheet, like that to be counted in another cell as above, also to follow the month year order

Dave
0
Hello,

This post is a follow-up to two previous threads located here:

Extract certain items from one list to create another list in Excel (closed 2017-10-28)

Understanding a few parts of an array-entered formula in Excel (closed 2017-11-21)

Briefly, the solution to those threads consists of an Excel formula which selectively extracts certain items from a source list and displays the extracted items in a new list while maintaining the correct order of the extracted items. Following is the solution formula and a screenshot from the attached file:

=IFERROR(INDEX(B$1:B$13,AGGREGATE(15,6,ROW(C$4:C$13)/(C$4:C$13=F$2),ROWS($4:4))),"")

Open in new window

2017-12-10b.png
Now I'm wondering how to modify the above formula to allow for a date column in the source list (which may or may not be in order chronologically) and then display the results in a list which is ordered by date as shown here (eg Abigail was listed first in the previous result lists but is now listed last because her associated date is later than the others):

2017-12-10c.png
As shown in the next screenshot, the attached file contains solutions from the two previous threads (AutoFilter 1 and AGGREGATE() 1) and accompanying notes (which are not directly related to the current thread) as well as a new sheet tab, DisplayByDate from which the previous screenshot was taken.

2017-12-10d.png
Thanks

AutoFilter_array-formula-evaluation.xlsm
0
While trying to find out how VLOOKUP with conditional formatting.  I'm using a random picker that highlights the random name and would like to pull the highlighted name to another sheet/cell, etc.
While reading the post with the same Title I've done a work around for similar by formatting the destination with if not empty or what ever condition that will highlight the destination when in place.
As a rookie, I'm still trying to learn how to do the task above.
Thank you in advance,
D, Smith
0
I have two columns (A and B) on a sheet in Google Sheets (and my local copy) that when changes are made in any of these, an e-mail notification should automatically be sent to person A.

Then I have two other columns (Y and Z) on the same sheet that when changes are made in any of these, an e-mail notification should automatically be sent to person H.

Is this possible?
0
Cloud Class® Course: C++ 11 Fundamentals
LVL 12
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

Hi everyone,

I need help on a worksheet I am working on in Google Spreadsheets.

I attached the screenshot of the document I am working on.

2017-08-03_15h16_45.png
1, 2 and 3 have been imported from another Spreadsheet via the Import Range function.

In 4, I need to average 1, 2 and 3, but it's not working because the formula is not reading the value, but the import range formula.

Any help would be appreciated.

Thanks!
0
I have a database containing data for about 20 quality measures for a about 80 physicians.  I have a workbook built that lists the providers names, email addresses, clinic, etc.  I have pivoted the database to create spreadsheets for each provider for one measure.  (I will duplicate this for each measure).  What I can do is run a macro I've created to automatically email each physician their associated worksheet, based on the their email which I've placed on their individual worksheets.  This sends them a read only version of excel as an attachment to their email.  What I would like to do is have my macro embed a snapshot of the worksheet into the body of the email, instead of sending it as an attachment, but I'm not quite sure how to do that.  I'm not very excel savoy and am happy I have gotten this far.  Below is the macro I am currently using and I need to know how to edit this to get what I'm after.  I appreciate any help!  Thank you!


Sub Mail_Every_Worksheet()
    Dim sh As Worksheet
    Dim wb As Workbook
    Dim FileExtStr As String
    Dim FileFormatNum As Long
    Dim TempFilePath As String
    Dim TempFileName As String
    Dim OutApp As Object
    Dim OutMail As Object

    TempFilePath = Environ$("temp") & "\"

    If Val(Application.Version) < 12 Then
        'You use Excel 97-2003
        FileExtStr = ".xls": FileFormatNum = -4143
    Else
        'You use Excel 2007-2016
        FileExtStr = ".xlsm": FileFormatNum = 52
    End If

    …
0
hi I'm taking destination as a lookup reference I taken ID in reference table and nomatch ouput map to destination ,partial cache mode I'm using ,I'm getting duplicates to destination
0
I have already make a "XML Maps" Mapping list under the XML Sources.
It works file when I do the XML import to the Excel sheet.

However, I would like to add the file name of the XML which imported to the excel.
Can anyone help me on this?

Enclosed with the Excel sheet with the mapping list included
https://drive.google.com/open?id=0B-cvVcO00sRvVldicS1kWFhGSGs
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.