[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

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
PMI ACP® Project Management
LVL 12
PMI ACP® Project Management

Prepare for the PMI Agile Certified Practitioner (PMI-ACP)® exam, which formally recognizes your knowledge of agile principles and your skill with agile techniques.

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
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
Starting with Angular 5
LVL 12
Starting with Angular 5

Learn the essential features and functions of the popular JavaScript framework for building mobile, desktop and web applications.

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
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
OWASP: Avoiding Hacker Tricks
LVL 12
OWASP: Avoiding Hacker Tricks

Learn to build secure applications from the mindset of the hacker and avoid being exploited.

Hi,
I would like a formula to round an if statement
Please refer to attached
Many thanks
Ian
round-if-statement.xlsx
0
The date/time stamps in column C and D should change based on the + or - value that is inserted into the colored cell in column I

The sheet I am experimenting with is here https://docs.google.com/spreadsheets/d/1q_FcbxZmY9vGRzyggmPQQpinwzH8BFQwQIqBA9Op9SA/edit#gid=1893308266
0
Assuming that the data below is in columns C and D and I want to show the elapsed time in terms of hours and minutes (h:mm).  How do I write a formula that fills that in when it sees a date/time format such as the one shown below in columns C and D

Column C                               Column D
Fri 02/9/2018, 8:14 PM      Fri 02/9/2018, 8:15 PM
0
Hi,

I would like a formula modified to avoid getting divide by zero errors. The formula was kindly provided by a member yesterday however
since using on a new spreadsheet containing zeros it throws up the errors.
Please refer to attached spreadsheet   The zero is highlighted Sum-Probabilities-to-one-V2.xlsxin cell B17
Many thanks
Ian
0
Hi,
I would like one formula to sum probabilities to one within an Index Range.
Please refer to attached sheet
Many thanks
IanSum-Probabilities-to-one.xlsx
0
Hi,
Can you please help with AND/OR function
LOGIC
IF A1 = abcd  AND B1 = blue  then 99
OR
IF A1 = abcd AND B2 = yellow then 99
otherwise zero  
Many thanks
Ian
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.