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

This version of the expression extracts the email address from a string of characters contained within cell E2 and replaces those characters with the result.

I also need versions of the expression that will extract the first name and one to extract the last name.

The email address can possibly be defined as any string of characters that are separated by an @ symbol with no spaces and surrounded entirely by either a (parenthesis) or <chevrons>.

This should work in a google sheet such as this one here https://docs.google.com/spreadsheets/d/1rQ5QC6Ipr5kkBDuNnIMY05Z09Q4q7lqZ52vd0bfCvtY/edit#gid=1895941459

=Regexextract(E2,"[A-z0-9._%+-]+@[A-z0-9.-]+\.[A-z]{2,4}")

Open in new window

0
Learn SQL Server Core 2016
LVL 13
Learn SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

Please can you help me with a formula to highlight duplicates within a range.


Column B contains Names of staff
Column C contains responsibilities of staff (example of days worked)

The duplicates are all to be found in column C, however I want the first need to look for the duplicates against the value they hold in B. example
Highlighted in yellow is what the formula would highlight "duplicate".

Column B will always be set out alphabetically. The formula will help me identify and then remove any duplicate instances in Column C.
0
Hello all,

I have two spreadsheet problems that I need to solve. Please.

1. How do I filter out a specific cell value in order to calculate an average value for the cells with numbers?
2. The ARRAYFORMULA / AVERAGE function includes blank cells into its calculation. As a result, blank cells result in an incorrect final average. See F44 as an extreme example. The final value should be close to 8, not 3.8.


The cell column can look like this:

8 (Great!)
8 (Great!)
4
5
4
4
5
4
7
4
8 (Great!)
8 (Great!)
8 (Great!)
8 (Great!)
8 (Great!)
7
DNA
DNA

Using the formula, =ROUNDUP(ARRAYFORMULA(AVERAGE(LEFT(A2:A19,1)+0)),1), I can calculate the average of all the values. However, this formula fails on any cells that include a "DNA".

Notes:

- Ideally, I would like to either filter within the Importantrange or the average function.
- I know how to use, "=filter(A2:A19, A2:A19<>"DNA")", but how can I incorporate the average and filter, or the importrange and the filter functions?
- Also, the way I am currently calculating averages, also factors in blank cells, which skews the final average. :(

---

I am including a sample worksheet, see tab "Filter out data".
In the sample worksheet, for simplicity, I am using "=ArrayFormula('Sample Data'!A2:E19)" instead of an "Importrange" function.

https://docs.google.com/spreadsheets/d/1Q2nN-MVD_iwJi2Se-PFWcNbMGb-EF6gQWQCruhnWW-Q/edit#gid=2077598250


Thank you
0
I'm having one of those moments where I cannot figure out what should be very simple. Help please. :)

From a survey, I have asked multiple people to rank something from 0% to 100%.

The results are something like this:

        A          B
01   60%     3  
02   70%     2
03   80%     9
04   90%     2
05  100%    1  

Column A is the percentage value. I need to determine the average % based on the number of votes in column B.

I am trying to calculate the average distribution value. Just 'eyeballing', I expect the value should be about 78%.

How do I calculate this value in a spreadsheet?

I created a scratch spreedsheet to assist.
https://docs.google.com/spreadsheets/d/1Q2nN-MVD_iwJi2Se-PFWcNbMGb-EF6gQWQCruhnWW-Q/edit?usp=sharing

Thank you so much. :)
0
Hi,
Recently when closing an Excel file  I have been getting the attached message relating to One Drive.
I have uninstalled One Drive  and rebooted and still get the window.
Can anyone please help me to get rid of whatever is activating this window.
Many thanks
Ian
2019-09-10-22_09_10-Window.png
0
I have used a formula to rank data.  When I try to sort from low to high based on rank, it does not sort correctly.  Any solutions?

=RANK(I53,$I$53:$I$69)

Data type is set to Number

Total      Rank
75.00      1
66.25      3
43.75      14
53.75      12
65.00      5
57.50      8
57.50      8
30.00      17
60.00      7
65.00      5
32.50      16
37.50      15
47.50      13
56.25      10
56.25      10
75.00      1
66.25      3
0
Q: How to unite interaction columns from R emmeans package dynamically generated in R-Shiny?

I am building an R-Shiny app where I need to wrangle the output from the 'emmeans' package. However, in this interactive environment where many factors may be entered by the user, the single-tibble 'emmeans' output structure will vary with each run depending on the selections made. It could go from having only a single main effect to having multiple 3-way interactions (mixed with main effects and 2-way interactions) arranged in a wide format way.

For instance, assuming the user selects FctrA (with levels A and B) and FctrB (with levels C, D, and E), the interaction FctrA_FctrB will be automatically considered as well. When (~FctrA, ~FctrB, ~FctrA+FctrB) are submitted to 'emmeans', the output tibble is structured as follows:

- the leftmost side of the tibble contains FctrA results (levels, estimates, SE, df, CLs);
- the certermost block contains FctrB results;
- the rightmost side of the tibble contains the interaction results

So far so good except that FctrA levels columns is a single column, FctrB levels column is also a single column, but the interaction portion has its levels split into two columns, one with FctrA and one with FctrB.

The above issue impairs gathering, spreading, stacking of the separate blocks owing to the dimensional discrepancy.

My question is: How can I tell Shiny ('tidyr') to find those split interaction columns and concatenate them …
0
I have 2 spreadsheets in xl 2016. When I copy a date from one workbook to another, it loses the format so for instance if I have 8/16/2014 on one sheet, when I go a copy and paste to another sheet in another workbook, that date becomes 8/17/2018. It adds 4 years and 1 day to the date. How do I get it to show the proper date when I copy from one workbook to another?

Thanks,
0
Hi,
I would like a formula to sum range of values between the set of zeros and place sum amount in each adjacent cell
Please reger to spreadsheet
Many Thanks
Ian
SUM-RANGE.xlsx
1
Hi,
I am puzzled why I get a #Value error when I extend a range beyond 1,000
please see attached.
Can someone please modify it
Many thanks
Ian
Value-Error.xlsm
0
Starting with Angular 5
LVL 13
Starting with Angular 5

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

I have a column of dates, in Column A, in the format "mm/dd/yy".
The column ranges in random dates from May, 2014, through today.
I'd like to simply count the number of cells that contain a certain year, in Cell B1.
Can I do this without having a helper column (of only the year itself), or without an array formula?
Thank you.
0
Hi,
I would like a match condition added to an existing SUM formula.
The sum formula sums a range of cells in 3 sheets.
There are two columns requiring formulas.
Please see attached.
Many thanks
Ian
matchdata.xlsx
0
bubble-chart.xlsmHello,

Is there a way to add labels to each of the bubbles in a bubble graph in Excel?

I understand that a bubble graph enables one to view a graphic depiction of three variables (x on the horizontal axis, y on the vertical axis, and z represented by the size of the bubble). In the following screenshot, four different sets of points are represented based on three columns of data (x,y,z) for each.

2019-07-31a.png
Is it possible to add a 4th column (to each set) which contains a name or label for each data point (bubble), and have that label displayed on the corresponding bubble itself? If not, is there a workaround or VBA code that will do it?

Thanks
0
Hello, it's hard to put this in words but I'll try. There's also an example attached to help with the visual

I have a data dump of employee(s) various employee dates (U3 service, U5 quota and U9 Ret Contri),  The values were dump all in separate rows, but what makes them unique is the Pers.no joined with the Prior Term Start Date.  Each combination has a U3 and date on one row a U5 and date and a second row and possibly a U9 date on a 3rd row.  I need to get all three of these dates in one row


Currently

Pers.No Prior Term Start Date    UT     Date  
1             9/14/2018                      U3     4/22/2007
1             9/14/2018                      U5     9/15/2018

How I need it to look

Pers.No Prior Term Start Date    UT     Date         U3                 U5                U9
1             9/14/2018                                           4/22/2017       9/15/2018
2018-to-Jul-2019-Salaried-Rehires--.xlsx
0
Hi,
I would like a formula modified to remove a space at the end of a name
Please see attached sheet
Many thanks
Ian
Trim-space.xlsx
0
Hi,

Is there a formula to reduce numbers to fit a predetermined range of high and low numbers ?
Please see example attached
Many thanks
Ian
Number-reduction.xlsx
0
I have a C# application that reads Excel spreadsheets.

using System.Runtime.InteropServices;
using Excel = Microsoft.Office.Interop.Excel;

I have these two references in the code. This application needs to have Excel installed. My question is there a way to not have to install Excel? Is there some abbreviated install package that could be run that will install just enough to allow the code to run?
0
I am using the formula below in conditional formatting to say that if you find a value that matches any of the cells K1364 through K1489 then format the cells in a range yellow. It appears to be working for the most part. But I need to be 110% certain this is working as intended. A few of the rows I am not seeing what got matched. My question: Is there a way to see what matched to give a cell its conditional formatting? Perhaps I could create another column to the left of my range "matching cell" or "matching criteria" - something like that. Any expert takers?

=SUMPRODUCT(--(ISNUMBER(SEARCH($K$1364:$K$1489,$A2))))>0
0
My customer just called me and said she not sure what she did but excel automatically inserted a percentage column next to each column in her spreadsheet. She wants to do for all of her spreadsheets but doesn't remember what she did? I can't seem to see what or how she did it? any suggustions?

Thank you.
0
Amazon Web Services
LVL 13
Amazon Web Services

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

Hi
I would like a formula modified to remove an apostrophe
Please see attached
Many thanks
Ian

outcome should be
Meccas Gift
remove-apostrophe.xlsx
0
Hi,
I would like a formula to count indexes.
Please see attached.
Please supply formula in yellow column
Many thanks
Ian
countindex.xlsx
0
Hi,
I would like a formula please that can convert text (fractions) into decimal
example
13/2    6.5
Many thanks
Ian
0
Hi,
I would like a Match formula for attached
Details attached
Many Thanks
Ian
Comp-match.xlsx
0
Hi,
I would like a formula to convert data from a forum site as below to do two functions
1 count number of rows for each name  example  Phillc-too  5   wit 8
2 Display name in next column to number as in example.
Is it possible in Excel ?
Many thanks
Ian

 phillc_too    (2037.   Posted 3-Jul-2019 Wed 13:23)        
14:30 - Bill Neigh
15:00 - Rock N` Stones
16:00 - Ocean Air
17:00 - Billy Dylan
17:35 - Kaizer

wit    (2036.   Posted 3-Jul-2019 Wed 13:22)        
2:00 Lady Kinsale
2:30 Lady Celia
3:00 Another Lincolnday
3:30 Parys Mountain
4:00 Dark Crystal
4:30 Newspeak
5:00 Robot Boy
5:35 Strategic
0
Hi Experts I have a Workbook that has 2 sheets I need to Vlookup a value from one sheet from a X-Y axis.
User selects sheet 2 to select flavor from one drop down (x) and selects second flavor from dropdown (Y)
Then a value of the intersection will show in the value cell.
Please see attached Workbook
X_Y-VlookUp.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.