[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

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 customers using Office 365 that are experiencing issues with the formatting of Excel.  One user creates the spreadsheets for distribution, some employees can view and print them as they were created but some must edit the document or it displays and prints with the top of the next page on the current one.  All of their settings are the same, views, margins, etc.  We have even looked at printing to the printer of the creator but the same issue is there.  What setting(s) are we missing that the users might have set differently?
0
Exploring SQL Server 2016: Fundamentals
LVL 12
Exploring SQL Server 2016: Fundamentals

Learn the fundamentals of Microsoft SQL Server, a relational database management system that stores and retrieves data when requested by other software applications.

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 have a user that saves a networked excel spreadsheet using excel 2013. They save it as a 97-2003 workbook. At that point it opens just fine on the 2013 excel but users that have 2007 it only shows a blank page.
I have done the following:
  1. unregistered excel with excel /unregserver
  2. i have unchecked DDE
  3. I have repaired office
  4. I have removed the XLStart folder and let it recreate it
nothing seems to work. Oh and the 2007 system is running the compatability pack and is able to open other excel spreadsheets just fine.
Thanks for your help
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
I need a function for column K of Sheet1, that says "If the row of Sheet2 contains the same RID number as a row in Sheet1, then fill Sheet1 with the email address located in column K of Sheet2.
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 a way to make all Excel spreadsheets open in the same instance, instead of opening a second, third instance of Excel?

For example, if open a saved spreadsheet, then I have one doc open. Then if I go to file, open and open a different worksheet, I want it to open in the same instance.


I am using Excel 2016.
0
Angular Fundamentals
LVL 12
Angular Fundamentals

Learn the fundamentals of Angular 2, a JavaScript framework for developing dynamic single page applications.

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
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
I am currently using the following LOOKUP formula in spreadsheet EE_Pub (columns N, O and P) to find 'last match' values in spreadsheet EE_Cat (columns J, K and L), unless the value in column K of EE_Cat spreadsheet = "No Change" or "CX'd" in which case it ignores these rows.

=IFERROR(LOOKUP(2,1/('C:\Temp\[EE_Cat_Test.xlsx]Sheet1'!$I$2:$I$1200=$M2)/('C:\Temp\[EE_Cat_Test.xlsx]Sheet1'!$K$2:$K$1200<>"CX'd")/('C:\Temp\[EE_Cat_Test.xlsx]Sheet1'!$K$2:$K$1200<>"No Change"),'C:\Temp\[EE_Cat_Test.xlsx]Sheet1'!$J$2:$J$1200),"")

I need to update the lookup formula to incorporate additional conditions:

If Column L of EE_Cat spreadsheet (Stage_4) column is blank, use the current lookup formula that finds last match:

=IFERROR(LOOKUP(2,1/('C:\Temp\[EE_Cat_Test.xlsx]Sheet1'!$I$2:$I$1200=$M2)/('C:\Temp\[EE_Cat_Test.xlsx]Sheet1'!$K$2:$K$1200<>"CX'd")/('C:\Temp\[EE_Cat_Test.xlsx]Sheet1'!$K$2:$K$1200<>"No Change"),'C:\Temp\[EE_Cat_Test.xlsx]Sheet1'!$J$2:$J$1200),"")

If Column L of EE_Cat spreadsheet (Stage_4) column contains a date, then lookup formula needs to change from last match, to row that has the latest date in EE_Cat spreadsheet (Stage_4) column (based on Column M value in EE_Pub-Test spreadsheet as per the current lookup formula).

Note that Column L is always formatted as: AA–08 Jan 16  13:02

I have provided the two sample spreadsheets. I have highlighted the cells and included comments on the EE_Pub spreadsheet containing the results I am looking for with the …
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
Learn Ruby Fundamentals
LVL 12
Learn 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.

How to import live data from redshift (database) to google?
Currently im using metabase as my sql studio or a tool but not able to fetch live data to google sheet to feed my live dashboards
0
Hi,
I would like a formula to detect a word within a string of words.
example:  Detect the word  'pear' from the following situated in cell C2     I picked a pear  (or)  I picked a plum
Return 1 if word exists and zero if it doesn't.
Many thanks
Ian
0
I need a formula if it sees HD in a cell it counts it as .5 and the other characters are counted as 1.

V= 1
P = 1
S = 1
H= 1
HD = .5
TI = 1
0
Hi,
I would like a formula to establish identical values within an index range.
Please see attached
Many thanks
Ian
identical-values-within-range.xlsx
0
get data from different spreadsheets ( in a folder) to a single file

I have many files in a folder, I need to copy data and paste it to single workbook. I have worked, I have worked little bit on a macro by googling but needs help

I am attaching the input and output files
sample_-Input.xlsx
sample_output.xlsx.xlsm
0
Hi,
I would like a formula to round an if statement
Please refer to attached
Many thanks
Ian
round-if-statement.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.