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 need to use the sumif function to do the summation for each change in column z in the attached excel sheet
sumif.xlsx
0
Cloud Class® Course: Microsoft Azure 2017
LVL 12
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

Wanted to calculate Column J based on what's in Column H.  Also Column K based on what's in column I.  I've added some calculations manually to illustrate what it should look like in yellow.

See Attachment.
C--Users-lfreund-Desktop-COUNTING-B.xlsx
0
I wanted to count all the "cancelled" for 2018 in column D for each of the dates listed in column G and have the results on Column J for each date.

See Attachment.
C--Users-lfreund-Desktop-COUNTING-B.xlsx
0
Wanted to see if there is an easier way to count dates from two columns.  I have several hundred rows of data that I would want to count dates from.

On column B (Create_Date) and column D (Complete_Date) i wanted to count how many times for example 6/1/18 showed up and for future dates coming up.  I did the manual count on columns G and H to illustrate what I was looking for but wanted to see if this can be done automatically.

See Attachment
C--Users-lfreund-Desktop-Excel-COUN.xlsx
0
Is it possible to create a hyperlink to a specific cell in a spreadsheet like Google Sheets or Excel?
1
Hi,
I would like a formula modified to ignore zeros or blank cells.
please refer to attached sheet
Many Thanks
Ian
0
I need help with a formula in Microsoft Excel.

Here is a sample of the data I need to report on:

Name      Date      Cost      Paid      Margin
BLUE      6/1/18      11.53      29.41      $17.88
BLUE      6/1/18      7.7      8.98      $1.28
BLUE      6/1/18      19.68      29.47      $9.79
ORANGE      6/1/18      0.33      0.98      $0.65
BLUE      6/1/18      0.65      0.92      $0.27
BLUE      6/1/18      5.57      39.99      $34.42
BLUE      6/1/18      3.59      41.96      $38.37
RED      6/1/18      199.13      209.36      $10.23
RED      6/1/18      12.67      16.8      $4.13
YELLOW      6/1/18      60.17      27.54      ($32.63)


From the example above - I need to find out the average profit per transaction for each "color" in the first column.

There are over 50,000 lines of data.and 137 different "colors".

So ultimately I need a line that says:
BLUE            6      $17.00

where 6 is the total number of transactions for BLUE and $17 is the average margin per transaction.

I would prefer not to use VBA or any programming language unless it is absolutely necessary.

I would prefer formulas/nested formulas is possible.  Thanks!
0
Hi. I am trying to do something fairly simple in Excel 2010 using Vlookup but ran into the 255 character limit.  I checked posts online and found that index/match should work to do the same functionality without the 255 character limit but I am still getting an error.  

I have attached my test Excel file but I also attached an image showing the sheet, as well as the formula displays.

vlookup and match testing
testing_excel_vlookup_070518.xlsm

I have tried many iterations of index and match but I can't get this to work.  Can anyone see what I am doing wrong?

Thank you!
Alexis
0
I have 2 sheets below and need a formula in the last Column (Cap Date) on sheet 2. When the Capped days (Persistent number) = the total count of a UPC from sheet 1 to return  sheet 1, Column2, scan date.  


Sheet 1
UPC      Scan Date
10      01/01/18
15      01/01/18
20      01/01/18
25      01/01/18
10      01/02/18
20      01/02/18
25      01/02/18
10      01/03/18
11      01/03/18
12      01/03/18
15      01/03/18
25      01/03/18

Sheet 2
UPC      Total Scans      Capped at (Days)      Cap Date
10                             3                            2      01/02/18
11                             1                            2       
12                           1                            2  
15                              2                           1              01/01/18
20                              2                           1       01/01/18
25                              3                           2       01/02/18
0
How can I remove or truncate the spaces or what I assume are carriage returns within the column? I attached an example.
Capture.PNG
0
Cloud Class® Course: Microsoft Windows 7 Basic
LVL 12
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

Dear Experts

We have marketing, sales, customer care, projects, legal, finance , accounts , admin, HR and many stake holders for each department and also management team who would like to know the daily/real time, fortnightly reports from every departments and from every branch office like region wise and all put together i,e  consolidated report and finally COO/CEO/CFO and MD get the numbers report,  though we have multiple systems like for sales and marketing SFDC and for some entry level ERP and few departments are operating with spreadsheets, we are looking for the solution where reports and associated data mainly reports from many systems like SFDC, ERP and spreadsheet all the reports of these are hosted in some location and by using some solution and create the reports and accessible to the stakeholders, please let me know for this process automation what solution would be a best while I understand we have to integrate applications/integrate with BI tool but this would take much longer time we are looking for some interim solution, at present we are using nextcloud for file share and sync can we ask users to palce their final versions of data in the folders and then use a solution like share point /ms access/Microsoft SQL server to achieve this process automation and reports for all the stake holders , not sure if sharepoint would be the solution, can you please suggest software solution for this so that we can go for that and search for software person skilled on …
0
Parse string to the right Example: "Welcome  Agency Members" Need to extract "Agency Members" to display
0
Compar-Template.xlsxHello Team,


I am in mid of Excel VB Script task.

I have a column A in Sheet1 & Column A in Sheet2 & Column c in Sheet3

have done VLOOKUP for Sheet 1 & Sheet2 (both the Column A's) & got the output result in Column B on Sheet1 & Sheet2 respectively.

Now the task is:

Matching VLOOKUP on Sheet1 & Sheet2, needs to appear in Sheet3 Column C with the help of VB Script, can this done, i am trying to get this out put, not able to find the correct out.
Pls advise.

Regards.
0
I want to set up a dropdown box that has a list of years that automatically populates with the year and a list of dates (week start dates) for that year.  I want them to be in the same set of cells as the plan is that these cells will be linked to date headings in other sheets. There are one or two dates that start at the end of one year. (row 57). I've set up an example in the attached file.
What I want to happen is that when a user clicks on the yellow cell eg. C1and chooses a year e.g. 2018, the entries in B5:B57 automatically autopopulate with the week start dates for 2018. If they choose 2019 from the list, the same range B5:B57 would autopopulate with the entries in E5:E57.  Thanks!
EE_Date_Sequence.xlsx
0
What is the parse formula to extract name from this string?

I have a column in excel with this data on each row.
"CN=John Doe,OU"

I'd like to extract John Doe and list it in another column.

I think Mid function can be use here?
0
I'm working in Excel 2007 and have been attempting to use VLOOKUP to resolve my problem.  I just haven't been able to make it work.
The attached workbook has spreadsheet with demo data.
The spreadsheet named "Contacts" contains a complete list of Contact Codes in Column A.
     a) There are 36,363 of these codes.
     b) Columns B, C, D, & E contain data, but is not essential for what I'm trying to do.
     c)  Columns F, G, H, & I contain no data now, but should populate when a correct formula is created.
          These columns are named the same as the other spreadsheet tabs at the bottom of the workbook.
I need a formula that will look at the individual Contact Code on each row of the "Contacts" spreadsheet, then:
     1) compare it to the Contact Codes in Column A of the spreadsheet named "AR Number"
          a) If the Contact Code is matched, return the "armaster acc no" shown in Column B to the "Contacts" spreadsheet Column F - named AR Number.
          b) If the Contact Code is not matched, return the word "None" to the "Contacts" spreadsheet Column F - named AR Number
     2) compare it to the Contact Codes in Column A of the spreadsheet named "AP Number"
          a) If the Contact Code is matched, return the "apmaster acc no" shown in Column B to the "Contacts" spreadsheet Column G - named AP Number.
          b) If the Contact Code is not matched, return the word "None" to the "Contacts" spreadsheet Column G - named AP Number
     3) compare it to…
0
Good Morning,

I'm trying to take this formula and transfert it to vba excel in a module.

=SI([Date de livraison]<>"";SI([Date cédulée]<>"";[Date de livraison]-[Date cédulée];"");"")

I have change the formula this way :  Selection.Formula = "IF([Date de livraison]<>"",IF([Date cédulée]<>"",[Date de livraison]-[Date cédulée],""),"")"  and it is still not working.

Can you help me to get this work please.
Thanks
0
Hi

I have an excel spreadsheet File1 containing a blank Country Codes column (A) and Country names column (B) + other data Several Thousand rows so the same country name will appear 0, 1 or multiple times

I have a second excel spreadsheet File2 containing both Country Codes column (A) and Country names column (B)  

is it possible to loop through the all the rows of File1 using the value,  Country name, in column (B)  to  search File2>column (B) return either column (A) value or Not Found and update column (A) in File1?


If it's easier I can copy the values from File2 temporally into File1 or create a VBA hash (if there is a thing)

Sample bellow with desired output

File1

Code	Name	Foo	Bar
	Argentina	other stuff 1	other stuff 2
	Argentina	other stuff 1	other stuff 2
	Aruba	other stuff 1	other stuff 2
	Argentina	other stuff 1	other stuff 2
	Australia	other stuff 1	other stuff 2

Open in new window

File2
Code	Name		
AR	Argentina		
AM	Armenia		
AA	Aruba		
AT	Ashmore and Cartier Islands		
AS	Australia		
AU	Austria		
AJ	Azerbaijan		
	

Open in new window

           
      
File1 Output            
Code	Name	Foo	Bar
AR	Argentina	other stuff 1	other stuff 2
AR	Argentina	other stuff 1	other stuff 2
AA	Aruba	other stuff 1	other stuff 2
AR	Argentina	other stuff 1	other stuff 2
AS	Australia	other stuff 1	other stuff 2

Open in new window

0
Hi I need following comment "Deletion flag" Column. Let me know the formula.

Material                                Colum 1                Colum 2       Deletion Flag       Comment
ACCACRI1061200       5402870301                                           Active            Since b3 & c3 filled
ACCACRI1061200       5560783462                5560783462     Active      
BCVPVCU040G031       5404237468                                         Delete           Since c4 and c5 is blank
BCVPVCU040G031       5404237469                                         Delete      
BCVPVCU040G034                                                               Delete              Since b6 and c6 is blank
0
Cloud Class® Course: Microsoft Exchange Server
LVL 12
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

I have a simple formula in Excel for extracting the initials from a name in column A to column B (below).

=LEFT(A2)&MID(A2,FIND(" ",A2)+1,1)

However, until a name is entered into A column, the B column is showing a #VALUE! error.

The first column is blank until an administrator completes it.

How can I hide this error?
0
Wanted to create a scroll chart based on the attachment.  Just wanted to show 5 departments at a time as you scroll.

Please see attachment.
C--Users-lfreund-Desktop-Scroll-Cha.xlsx
0
In Excel I need to be able to compute the number of times there are more than one unit on an incident and how many units end up on that incident.  I have start and end (date/time) in separate columns.  I'm guessing an array of some kind but not sure how to determine number of units on an incident
0
Hi,
I would like the attached formula modified to convert any zeros to 5
please refer to attached worksheet.
Thanks Ian
Match.xlsx
0
Hello experts,

I have the following table.2018-06-24-11_51_11-Microsoft-Excel-.png
I am looking for a sumif formula in order to have the monthly benefit by month.
For example for April I should display the monthly benefit (income-expense) by month.

Could you please help to set up the formula?

I attached xlsx file.

Thank you very much for your help.
test-monthly-sumif.xlsx
0
Hi,
I would like an index/match formula. Please refer attached sheet
Thanks
Ian
Match.xlsx
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.