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

My brain just froze. I would like the AND/OR formula for the following please
C1 MUST Contain "RED"
D1 can contain "APPLE" OR "PEAR"
Destination cell A1
Many thanks
Ian
0
Python 3 Fundamentals
LVL 12
Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

Wanted to count values based on certain criteria.   For example per the attachment.

The count is on the "MAT_REC_BY_MONTH" tab and the data is on the "DATA" Tab.  I've added some values on Week 1 and week 2 to show what the values should be.

On "MAT_REC_BY_MONTH" tab:  Column D (REC), the count is based on the "DATA" Tab Column Z filtered on "blanks" on Column AB for Calendar WK 1 on column Y.
On "MAT_REC_BY_MONTH" tab:  Column E (REJ), the count is based on the "DATA" Tab Column Z filtered on "YES" on Column AB for Calendar WK 1 on column Y.

Wanted a formula to count for all the weeks listed on the "MAT_REC_BY_MONTH" tab.  See attachment.
C--Users-lfreund-Downloads-COUNT-EE.xlsx
0
My goal: I select a set of rows and move that set of rows one column to the right. I think I can do this manually, but not efficiently. Attached is a short simulated spreadsheet of the one at work - it has nested loops and if statements. Even though it is short, its data structure doesn't jump out at you due to lack of indentation.
Nested-Macro-with-Merges.xlsm

The current huge table is very hard to read due to the heavy nesting of loops and if conditions. By indenting, I hope to make the spreadsheet easier to read.

The first column has a field name. This field may be a merge of 2 or more cells. Other columns associated with this field name may also have some merged cells of varying numbers. And that has caused me some problems when I try to indent.

Thanks in advance for your assistance. Now I wish I knew some VBA, so that I could give this a try.

Paul
0
Need help understanding vba in excel spreadsheet. Attached is a spreadsheet with an indent macro. I would like to learn enough vba so that I can try to modify it if need be.
 29130882d.xlsm
I would appreciate if the macros could be annotated with comments for the non-obvious lines. Here is what I find obvious:

Dim lngLastRow As Long
For lngRow = 1 To lngLastRow
Next
Exit For
End If
lngLoopEnd = lngLastRow
0
I'm getting an error on this line in a new console c# project:

using Excel = Microsoft.Office.Interop.Excel;

Severity      Code      Description      Project      File      Line      Suppression State
Error      CS0234      The type or namespace name 'Office' does not exist in the namespace 'Microsoft' (are you missing an assembly reference?)      excel1      E:\dotnet\excel1\excel1\Program.cs      2      Active

What do I need to be able to access excel spreadsheets from my console c# project.

Thanks in advance for any help.
0
I have a very large Excel table. I want to move some row regions one column to the right, but get an error when I cut and paste. See attached the spreadsheet.
Excel_Too_Many_Cells_Rev_A.xlsx

This is what I am given:


I would like to move all the rows between "Loop" (Row 1), and "End of Loop" (Row 11) to the right by one column. Here is my end goal:
This is what I would like the sheet to look like after I cut and paste
I try to cut the region A2:C7 (which includes B8), and paste it at B2, then I get the error message:
This operation will cause some merged cells to unmerge. Do you wish to continue?
If I continue, then I lose some text.

Since I have a very large number of rows, and have nested structures (e.g., nested loops, or if statements), to do this manually would take a very long time. Can you please advise how to accomplish this more efficiently.

And there are many nested conditions:

Loop on N1
...
If A33 = 1
...
Loop on N2
...
Loop on N3
...
End Loop on N3
...
End Loop on N2
end if A33 = 1
...
End repeat on N1

Thanks,
Paul
0
How can I create new columns based on specific cells and populate the data using Excel PowerQuery?  Table is not export in the best tabular format.  The attached file has the fields highlighted in yellow that I'm referencing.  Also, the date field, could Power Query create a column for month and year?
[embed=doc 1407617]
0
I am trying to link a table and graph from Excel into Word using Office Pro 2016 and these files are in the same local folder. I am copying the table from Excel and pasting using "Link & keep source formatting." Then I copy and paste the chart from Excel into Word using "Keep source formatting & link data."

With both Excel and Word documents open, if I update the data in the table it auto updates the chart in excel and the chart in Word. In Word, I have to right click on table, and select "Update Link."

This works great and is no problem, if it continued to work. Once the documents are closed and re-opened, they do not work. I verified documents are saved before closing. When I reopen either file, excel or word, the data will not update. There is no option to "update link" in Word.

I checked in Word under Options>Advanced>General that "Update Automatica Links At Open" is checked.
0
Hi,
I would like to speed up the following formula by adding the INDIRECT function if appropriate.
I would also like it to be changed from an array to a normal formula if possible.
refer question 'Speed up Calcs using INDIRECT function'.
Please see attached.
Thanks
index-formula.xlsx
0
Hi,
I would like help to amend a formula to speed up calculations with an INDIRECT function.
Please refer to attached spreadsheet.
Many Thanks
Ian
Indirect-Function.xlsx
0
Introduction to R
LVL 12
Introduction to R

R is considered the predominant language for data scientist and statisticians. Learn how to use R for your own data science projects.

Hi,

I would like to have the attached set of formulas modified in order to
increase the speed of calculation and to avoid unexpected shut down of worksheets.
The worksheet has just in excess of 100,000 rows.
I've identified that Column E is the culprit.
The ultimate object of the set of formulas is to establish z scores as shown in yellow col F.
Please refer to attached sheet.
Many thanks
Ian
modify-stddev-formula.xlsx
0
I was able to convert data from one source by using the following formula.
PROPER(SUBSTITUTE(LEFT(IG2,IFERROR(FIND("(",IG2)-1,LEN(IG2))),"'",""))
from
LAURA'S BAIRN(GB)      
to
Lauras Bairn
So I tried the same formula on data from a different source and it produces something different.
from
Laura`s Bairn
to
Laura`S Bairn

Any idea how to modify it ? to return same result as in top example    ( Lauras Bairn )
Many thanks
Ian
0
I am trying to utilize a SUMIFS function in the simplified table below.

Right-Test.png
In cell D2, I want to show the sum of all items in column D where column C equals "t" and column B ends with "?".  The formula I am stuck on is:

=SUMIFS(D:D,$C:$C,"t",$B:$B,RIGHT("~?",1))

Open in new window


I know the column C condition works properly, but I am missing what is wrong the the column B condition.  The right function seems to work if I use "RIGHT("AB",2)" but not with any single character.  Could somebody kindly point me in the right direction, please?  Thank you in advance!
Right-Test.xlsx
0
Hi,
I have had endless problems with Excel crashing or taking an age to calculate. I have just discovered that
it is due to curly bracketed array formulas. My worksheets often contain in excess of 140,000 rows.
Could someone please help me substitute the below formula to one without brackets even if a helper column is required.

{=STDEV.S(IF($A$2:$A$22=A2,$B$2:$B$22))}

Many thanks
Ian
0
Hi,
I would like a formula modified to include the index column
Please refer to attached
Many thanks
Ian
S-Dev-formula.xlsx
0
Hi,
I would like a formula to find average of a series of values.
Please see attached
Thanks
Ian
Average-value.xlsx
0
Hi,
I would like to rank values by minimum order.
Please see attached
many thanks
Ian
Rank-Order-by-min-value.xlsx
0
Is there an Excel ProductIF function?  
I basically want to reproduce a SumIf function whereby a value in a column corresponds to a value in a range to multiply against another column across from it containing a value .. in this case, i want to lookup a value in .. i've attached a screen shot of a simplified table which produces my answer in Column D.. BUT obviously, i want to change the value of range B11:B15 and have it refer to the matching % values in range C2:C8.  

I'm interested in dynamic what-if values in Column D.  
thanks!
cn
ProductIF.PNG
0
Hi,

Can someone help me with a formula to remove data within brackets and brackets.
from    Brown(GB) to Brown
from    Smith(IRE) to Smith
Many thanks
Ian
0
Become a CompTIA Certified Healthcare IT Tech
LVL 12
Become a CompTIA Certified Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

I am trying to import this relatively small table into the Google Sheets

=importxml("https://icsc.un.org/resources/sad/dsa/restr/history/History102018.XML","//table")

I am not familiar with the XML Xpath syntaxes.

All i want is to get the table in my google sheet.

any help is appreciated

what should i change in "//table" the second argument of the IMPORTXML function to be able to make it work?
0
Hello,

I am new to Excel's Power Query and am managing a monthly cumulative reporting process with excel files containing Power Queries and am not finding them intuitive to use. I need to filter a tab in one file, then transfer the filtered data to another tab that will continue to track these updates monthly while removing them from the current output. The data on the current tab in question is generated by an existing query.

In the original file, a query exists that updates the data on Miles >50 from another workbook. But, for my example file attached I have added a tab Miles <=50 that I am needed to move all records where the mileage is <=50 from the current tab.

This is the current state
Current Single tab "Miles>50"            
            
Area      Vehicle#                  Miles
1              1C4NJDEB6            11.7
1              5NPDH4AE        11.7
1                3N1AB7AP            11.7
1              3N1AB7AP            17.3
1              KNDJP3A59            62.5
1              1N4AL3AP2       62.5
1              5NPE24AF4            62.5
1              KNDPB3AC       62.5

--------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------
This is the TARGET State: Additional tab of "Miles<=50" containing records filtered and moved from "Miles>50" tab


Updated TAB "Miles>50"
Area      Vehicle#                 Miles
1              KNDJP3A59            62.5
1              …
0
Hi,
I would like an existing formula modified to convert text on two samples and end up with same output.
Please refer to attached.
Many thanks
Ian
textconvert.xlsx
0
Hello, I have this Excel document I've been struggling with for quite some time now. I would like to have row "N" through row "Q" highlighted in Red with White lettering based on two criteria. The number in column "O" greater than "0" and the Date in column "Q" equal to or less than the date in cell "M1". The reason for using the date in cell "M1" is that I put this date manually each afternoon and print the spreadsheet for the next day for the production line.

Thanks
Spreadsheet.JPG
0
I have an xls file called "Masterlist" that has hundreds of contacts, i.e. Member ID, first name, last name, phone, and email in respective columns/rows.

I have another xls template that only contains Member ID in a column.  I need a formula/method to auto fill/populate the remaining info, i.e. first name, last name, phone, and email per row that matches Member ID on the template file from the Masterlist xls file.  

I'm assuming it's a vlookup or lookup formula, but need a bit more direction and most efficient method.
0
How do I convert the data below using a pivot table. If this can not be done with a pivot, then what is the fastest VBA code that can do the same thing.

BEFORE

     A          B
Tommy    98
Tommy    90
Tommy    22
Larry        12
Larry        99
Billy          52
Joe            34
Joe            55
Joe            67
Joe            96


AFTER

Tommy   98   90   22
Larry       12   99
Billy         52
Joe           34   55   67   96
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.