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 another Excel puzzle that I am trying to solve.

I am trying to convert a name column into an email list. It sound simple enough, but there is a small twist, that is, some names included preferred first names in brackets, and some names have three names. :P

Note: The names the example spreadsheet are a good representation of the list I will use IRL:
- Only first and last name, if a Western name.
- May include a preferred name in brackets, if the name is non-English - the first name in brackets will be used with the last name.
- Some names have three components.

Examples:
Column A               ->  Desired name conversion

Bobby Robinson            -> bobby.robinson@company.com      - solution found
Lin Hon (Adam) Xiao      -> adam.xiao@company.com               - solution found
Lang Lin Lee  -> langlin.lee@company.com                                  - need a fix.

So the last scenario still needs to be resolved.

The current formula (thanks to Ryan Chong), doesn't take into account a name with three parts.
For example, a name like "Lang Lin Li", converts to lang.lin.li@company.com but should be langlin.li@company.com

I was thinking that I could parse the new solved names column with a =SUBSTITUTE(G9,".","",1), but it would also have to take into account to only remove the first instance of a ".", but only when there is more than 1 "."

Is it possible to add additional complexity to the existing formula?

Current formula:
0
CompTIA Network+
LVL 20
CompTIA Network+

Prepare for the CompTIA Network+ exam by learning how to troubleshoot, configure, and manage both wired and wireless networks.

I have another excel puzzle that I am trying to solve.

I am trying to convert a name column into an email list. It sound simple enough, but there is a small twist, that is, some names included preferred first names in brackets. :P
This means that the preferred first names must be used.

Note: The names are strictly using the the examples in the spreadsheet, there are no other variations:
- Only first and last name, if a Western name.
- May include a preferred name in brackets, if the name is non-English.

Examples:
Column A               ->  Desired name conversion

Bobby Robinson            -> bobby.robinson@company.com      
Lin Hon (Adam) Xiao      -> adam.xiao@company.com

In the example spreadsheet (tab = email addresses), I was able to each scenario separately, but not as a single unified formula.
https://docs.google.com/spreadsheets/d/1Q2nN-MVD_iwJi2Se-PFWcNbMGb-EF6gQWQCruhnWW-Q/edit#gid=833560184

Can anyone with better skills than me, help me with a solution?

Thank you
0
Sheet1Sheet2
I have two Google Sheets. Sheet1 is categorizing the "Places" from Sheet2. I want to populate the "Categories" column on Sheet2 with all the headers from Sheet1 whose columns contain the given "Place". For example, the "Categories" cell for "Alabama" should contain "Bands, States" (or whatever concatenation format can be done).
0
Hi there. In the attached file, I need to pull back the total amount of each user's donations using an Excel formula.

Any ideas experts?
sum-donations-per-user.xlsm
0
Hi,

I would like the following formula to be modified to return the last value.
The below formula returns the last cell whether value or formula cell containing no value

=ROW(OFFSET(A11,COUNTA(A:A)-2,0))

Many thanks

Ian
0
Hi Techs.  I am in a new role and am using Google Sheets.  I needed to make a template for my daily report. So I made a copy of the the sheet with its' tabs, deleted the data, saved that as my Template, and the original data was completely blown away.

How can I prevent this from happening again?

* I was able to get the entire deleted sheet back by using File > "Version History" > and then saving the last version I worked on.

Thank you,
Mark
0
Hi,

I have a very large database which I would like to divide into eight separate sheets. Reason being it is too large for the tasks I want to  perform.
For simplicity sake lets call the columns, Gender, Age, ID

The sort would be the following

1) Male, (age) <20, (id) <10
2) Male (age) <20, (id) >9
3) Male (age) >19,(id) <10
4) Male (age) >19, (id) >9
5) Female, (age) <20, (id) <10
6) Female (age) <20, (id) >9
7) Female (age) >19,(id) <10
8) Female (age) >19, (id) >9

The way I would normally do it takes forever.
If there is a much quicker way I'd be very appreciative.

Many Thanks

Ian
0
Hi,
I'd like a formula to select data where first letter in a name = U
example
                               formula
UABCD   46            =46
ABCD     32            = 0
ACDE     46            = 0
UACDE 50              -50

Many thanks

Ian
0
Greetings experts,

Here is a screenshot of the "Key-In Data" sheet I am trying to work with:
Capture.PNG
And here is my code:
function myFunction(){

var app = SpreadsheetApp;
 var activeSheet = app.getActiveSpreadsheet().getActiveSheet();
{var emailRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1").getRange("B26");
var emailAddress=emailRange.getValues();
{
var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
ss.getRange("E2").setFormula("=B2+C2+D2");

 for (var i=2;i <= 2;i++) {
 var currentCell = activeSheet.getRange (i,5).getValue();
   if(currentCell < 4){
   var message="Product has reached a critical value "
   var subject = "Update on transfusion product (EMERGENCY!!)";}
   else if (currentCell > 6) { 
   var message="Product has reached a normal value"
   var subject = "Update on transfusion product";}
   else 
  {var message="Product has reached a minimum value"
   var subject = "Update on transfusion product";}
  
  MailApp.sendEmail(emailAddress,subject,message);
  }

}
}

}

Open in new window


What this code does is that it will send an email based on cell E2. The logic is completely fine here and it does not require any changes.

What I do need help with are:
1. Right now, the code is pointing to cell B26 for the email. I need the email in the code instead.
2. I also want to add cell E2's value at the back of the Email message. For example, "Product has reached a critical value of 3".
3. Every time the code is run, a time stamp should be generated at cell A2.
4. After the timestamp is added, I need row 2 of "Key-In Data" sheet to be added to the next available row in the "Summary Data" sheet.

Here is sample test environment:
https://docs.google.com/spreadsheets/d/1kA1CH2I0Rho_GcvO_PqsWIAuZMZlK4BFukP-TsUlmyY/edit?usp=sharing

Any help is much appreciated! :)
0
Experts,

I have a group of values on a spreadsheet "Sheet 3"  Column A  Rows 2 =15 that I have assigned to a name field called 'East'.  Is there a way to check column "A" on Sheet 1 equal to one of the values in my named field and assign "EAST" to column "B' on the respective row.

Microsoft Excel 2016
Named_Feild.xlsx
0
HTML5 and CSS3 Fundamentals
LVL 20
HTML5 and CSS3 Fundamentals

Build a website from the ground up by first learning the fundamentals of HTML5 and CSS3, the two popular programming languages used to present content online. HTML deals with fonts, colors, graphics, and hyperlinks, while CSS describes how HTML elements are to be displayed.

Hi,
 
I would like a formula to find year in string.
Please see attached and place formula in A2

Many thanks

Ian
Year-change.xlsx
0
Hi,
I'd like a formula to covert the below time format to seconds
Many thanks
Ian


17:23:06
0
Hi,

I would like a formula to replicate from a certain cell in a series.
Please see attached.
I have placed the correct outcomes in column F which refer to column A (yellow)
Please use column G for the formula

Many thanks

Ian
cell-value-formula.xlsx
0
Hi,
The below formula finds bottom row.
Can someone please help me modify it to ignore formula cells.
I only want last cell that contains a value.

=ROW(OFFSET(A9,COUNTA(A:A)-1,0))

Many thanks

Ian
0
My background is as a computer programmer specifically using access databases and VB for apps. My new job requires me to work with spreadsheets in excel. I understand the fundamentals in regards to creating tables from a master worksheet in excel and manipulating the information, vlookups, etc. Part of my job will be updating and adding information onto the master spreadsheet from various tables created by various department. I have not worked with the master spreadsheet yet but understand that there are various macros and formulas embedded in the worksheet. My concern is that I know when adding or updating information via rows or cells that this can have an effect on the entire worksheet. I am maticulas and do not want any mistakes. How would you recommend the checking process prior to entering on a production worksheet? I understand checking for duplicates on the table, checking formating in table cells to match the master sheet, etc. The master sheet has a large volume of data and I don't want to make a mistake by updating one cell and affecting another. Thanks for taking the time to review and offering suggestions on a checklist to adhere to accuracy.
0
I want to export from an Outlook public contacts folder to a spread sheet or database were i can apply bulk changes and then import back into Outlook contacts. CodeTwo Outlook Export does a great job on the export to CSV including categories, created date, modified date, modified by, etc.  Outlook's native export does most but not all exporting to CSV.  I tried to load Contact Genie, but it is no longer available.   I'm using Office 365 and Exchange Online.  Does anybody know of a program that properly handles export and import of all the Outlook fields?
0
Hi,
I have multiple spreadsheets open at the same time sometimes 4-5.
Can anyone advise best specs for monitors for this purpose.
giving clearest crisp text.
Will 4K improve text quality ?
I will use a 3 monitor setup and will NOT use for gaming.
Everything on the net nowadays relates to gaming !!
Thanks
Ian
0
When I create an invoice for my customers, alot of my customers decide to pay me over time. Those that opt in to pay over time make a monthly payment to me. As I run some reports, I am trying to figure out two things: 1. How many months does it take for each invoice to get paid in full? 2. In how many months is 1/2 the invoice amount paid. I want to know this because when 1/2 the invoice is paid, I offer them other discounts. I attached a sample of my report I export. I added two columns (B and C). I put the answers in those columns that I am hoping you can help me with the formula. So, can you please help me with the formulas in columns B and C? Thank you in advance!!!
Data-Sample.xlsx
0
Hello, so I have this Excel table (facsimile):

Initial table
I've been asked to make it so that the table can be printed using the second column as a way to split it into more tables. So, the data should be grouped depending on the second column value.

So this table should be printed on 3 pages, and we'd get these 3 tables, one on each page:

3 tables, grouped by the value in the second column
Does Excel have some quick way to do this which I don't know of? Do you have any suggestions?

Thanks.
0
JavaScript Best Practices
LVL 20
JavaScript Best Practices

Save hours in development time and avoid common mistakes by learning the best practices to use for JavaScript.

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
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

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.

Top Experts In
Spreadsheets
<
Monthly
>