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

Hi Experts.  I have a table with three columns.  Column J is number of years (1-20) and K (plan a) and L (plan b) contain various percentages.

I need a calculation that reads cell c4 (plan a or plan b as inputs) and c6.  c4 is either the heading for column K (plan a)  or the column L (plan b).  c6 is a number of years (1-20).  I need a look up value returned from this table with the calculation returned in cell d12.
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.

Wanted to calculate status based on criterias in Excel.  On column H I have status based on what's in collumns C, D and G.  Please see explanations on column I.

Please see attachment....
C--Users-lfreund-Desktop-EE-STATUS-.xlsx
0
Hi guys

I'm completely new to VLOOKUP tables and have been working on some tutorials. I have an Excel sheet that I have attached so that you can see what I am trying to do.

So basically, in worksheet 'User Details' I would like the column 'Organisation' (column C) to be filled in with the data in worksheet 'Mailbox Details' called 'Organisation A (also column )'. If you look at cell C8 in 'User Details', you will see 'contoso.com' which I have manually put in there.

Here's how I am trying to do it:

In the 'User Details' worksheet, the column A6 is the value I am looking up. As an example, my Lookup_value will be 'A8'.
The Table_array will be worksheet 'Mailbox Details' columns 'C5:D29'.
Col_index_num = 1 as that's the column I am trying to extract data from.
Range_Lookup = false

The issue I am seeing though is that in 'Mailbox Details' worksheet, in Column C there is a formula that has been dragged down and perhaps this is causing the issue?

If you're able to help me figure this out, that would be grand.

Thanks for helping
Yashy
ActiveDevicesUsageTracker-07-06-201.xlsx
0
Hi guys

I have an excel sheet with a load of data in one particular column. It has lots of email addresses ending with various domain names. In the next column, I would like to create some sort of formula that outputs the domain names from the column with the data into the associating rows so that I can then organise it by domain name. So if cell A1 says 'lisa@abc.com i would like the next column in the associating row to output 'abc.com' and then do that for all the domain names in there.

Does this make sense? If so, can you help me write the correct formula to output the content correctly?

Thanks a lot
Yashy
0
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
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
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
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
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
Cloud Class® Course: Certified Penetration Testing
LVL 12
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

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
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
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
Parse string to the right Example: "Welcome  Agency Members" Need to extract "Agency Members" to display
0
Cloud Class® Course: MCSA MCSE Windows Server 2012
LVL 12
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

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
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 have a vertical list. See attached file. What formula would I need to use so that the entries are captured horizontally. For example in D5 I have 1/1/2018, in cell G5 I have 1/1/2018. What do I need to use so that if I change the entries in D5 to D20, they automatically update in cells G5 to V5. It's in Sheet 2 of this file.
EE_Date_Sequencev2.xlsx
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
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
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

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.