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

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
Fundamentals of JavaScript
LVL 12
Fundamentals of JavaScript

Learn the fundamentals of the popular programming language JavaScript so that you can explore the realm of web development.

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
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
I have two workbooks (Calling.xlsx, Source.xlsx); Calling.xlsx links to Source.xlsx

I would like to have the folder address update automatically in Calling.xlsx if the workbooks are moved to another folder; effectively auto updating links to the other worksheet.

I have found the following formula on Google somewhere: =LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1),1)-1)

     So this formula would return, for example, the following: C:\Users\User\Desktop


My problem is trying to concatenate it with a workbook, sheet and cell reference so that the formula would work like a direct link made manually:

     ='C:\Users\User\Desktop\[Source.xlsx]Sheet1'!$A$1
0
Hi,
I would like to combine a start and end Index formula with a sum probability formula
to greatly speed up the calculations.
Both formulas are displayed on the attached spreadsheet.
Many Thanks
Ian
Sum-Probabilities-to-one-Version-3.xlsx
0
Microsoft Azure 2017
LVL 12
Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

Hi, I'm using openpyxl to format cells in excel from 'General' to other builtin formats. Although the script completes and states the format has been changed, when I try and open the excel file, excel pops with and error box, repair or cancel.

If the report option is selected, then another pop up prompts read only or exit. If I select read only Excel crashes.

Is the below the correct way to set number formats using openpyxl?

runfile('/.../test.py', wdir='/...python/...')
Reloaded modules:

Text
Number
Accounting
Currency

import openpyxl
wb = openpyxl.load_workbook(xlfile)
ws = wb['Sheet1']
ws['B1'].number_format = 'Text'
ws['B2'].number_format = 'Number'
ws['B3'].number_format = 'Accounting'
ws['B4'].number_format = 'Currency'
print (ws.cell(row=1,column=2).number_format)
print (ws.cell(row=2,column=2).number_format)
print (ws.cell(row=3,column=2).number_format)
print (ws.cell(row=4,column=2).number_format)
wb.save(xlfile)

Open in new window

Screen-Shot-2018-09-02-at-17.44.21.png
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
I am looking to create a dashboard in google sheets. With functional buttons and charts. Are there many templates out there for Google sheets dashboards?
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
The company I work for recently upgraded from Office 2010 to Office 2016. Since the upgrade we've had multiple users across the company experiencing issues with Excel freezing particularly when working in file share hosted spreadsheets, no matter the size of the document. We narrowed down some of the issue when it occurred on local files as well by disabling the graphics hardware acceleration. Attempts to quick repair, online repair, uninstall/reinstall, different computer, etc. have been unsuccessful. It doesn't seem to happen all of the time but it does happen frequently.
0
How can I get the below "Desired Steps" #2 to work ?

Current Steps
1. enter "this is a test to delete THIS word I did by mistake" into CELL B1 in an EXCEL 2013 file
2. hit LEFT arrow on keyboard to delete the above "THIS" text
3. CELL A1 becomes active

Desired Steps
1. enter "this is a test to delete THIS word I did by mistake" into CELL B1 in an EXCEL 2013 file
2. hit LEFT arrow + something on keyboard
3. cursor appears behind the word "mistake", allowing me to LEFT arrow to "THIS" and backspace to remove "THIS"
0
I have a list of hours Hours 1/Hourse2 etc. I have formatted all the columns as [hh]:mm . I want to enter an if function that says they are entitled to holidays if their total hours is over 40. (which I have entered in L1). As you can see in column F, this If function isn't picking up the ones that are under 40. What am I missing?
EE_time_format.xlsx
0
I am working with a very arcane program that uses nested IF statements to calculate product quantity to consume.  The formulas are based on "choices" (hence the C1, C6, C7, jargon) made within a "configurator".  Not being an expert with the nested IF thing I have to create a spreadsheet with all of the criteria, color code all the possible answers and then work my way through it.  Usually I can stumble my way through these to a working formula... unfortunately...  I've run up against a formula that two of us here just cannot seem to resolve.  The formula has to be written   in the format
IF(C1=6,IF(C6<=2,IF(C7<=13,2.5,IF(C7>24,2,IF(C6>2,IF(C7<=13,.5,IF(C7>24,0,.....

Open in new window

as it does not accept the more Excel and SQL like IF, THEN, ELSE syntax.
Whatever help you can offer will be greatly appreciated as I've expended nearly all of the brain cells I have left on this.  :-)
Screen capture Excel cheat sheetIF-Fomula.xlsx
0
Become a Certified Penetration Testing Engineer
LVL 12
Become a Certified Penetration Testing Engineer

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

I'm trying to set up a spreadsheet that calculates commissions, using a sliding scale. Frontwards is easy: Take the value of the sales. Use the base commission, and add it to the sliding commission based on a lookup on the bonus, based on the sales value. No problem.

But I'd like to also be able to do it backwards. In other words, I want to know that if I want to make $X commission, then this is how much I need in sales. But it has to factor in that same sliding scale. And I don't know how to manage that. I'm attaching the spreadsheet here.Commissions.xlsx
0
Hi Experts,

    I have a worksheet that keeps track of a monthly house payment; Column B and C are the recurring monthly payment amounts. With each payment monthly, I'd like the formula to compute an ongoing total of equity paid which is reflected in column F.  Please see example screen shot.  Any help with this formula is greatly appreciated.
Example-for-Monthly-Equity.JPG
0
I am looking for a SW tool like Excel or AirTable that can handle records/lines that are subordinate to another record/line, Parent/Child.  Or even a database tool, if an average End-User can handle it by themselves.
For Example, like this in Excel:
  1    Car      Red      Mustangs        Convertible
  2                2015    11000 miles    $24,500      
  3                2011     37,500 miles  $21,750
  4                2013     22,000 miles  $19,000
  5   Truck   White   Tacomas         Ext Cab      
  6                2017    12,500 miles   $18,750
  7                2014    43,000 miles   $21,950
So in this example I have 3 Cars and 2 Trucks ... the issue is that I want to be able to SORT on Car or Truck and have those sub-items (like "2015  11000 miles  $24,500" stay together under each parent item.  And the option to show or hide those sub-items would be awesome, so I can print a Report either way !!!
Solution can be on Mac, Windows, Online, any method.
Thanks all in advance for any help!
- B
0
I asked this question: https://www.experts-exchange.com/questions/29112556/Merge-two-Excel-sheets-into-one-using-a-unique-key.html
But, then I learned that the key, i.e., the Acronym column is not unique. That is, one acronym can have multiple Term meanings. Please refer to the other question for the sample excel tables, and just add a couple of dupilcate Acronyms having different Term values.

What this means to me is that after I append one table to the original, I just would like to identify which Acronyms are duplicates. I would eyeball it, but I am already over 200 acronyms, and I just started this a couple hours ago.

The solution could be VBA, but if there are less typing approaches using Excel buttons, that would be easier, since the spreadsheet is in an internal network.

One non-essential plus would be for me to be able to mark some duplicates for delete, and then remove them in a few keystrokes (maybe sort on the Mark column, and then group delete manually?).

Thanks,
Paul
0
I Need to compare two sheets.  Fill in data on sheet ARMS column A with data from sheet Group columns C or B
Look at Group!E:E  and compare with ARMS!C:C
 If there is a match, then return what is in Group!C - if there is a match and nothing in column C then return what is in Group!B.
If there is a match and both columns are empty then return a '0'
If there is NO Match then return 'NO MATCH

Please see attached example
Compare2Sheets2columns.xlsx
0
I am given an Excel Spreadsheet with a single sheet, "Original", filled with Abbreviations (mistakenly, I called it Acronym). I added more abbreviations in a "New" sheet. Now I want to merge the two sheets into a "Merged" sheet. Since my "New" abbreviations, LOL and ACK, are duplicated in the "Original", I do not want to include them in the "Merged" sheet.

Shows Original sheet, my new sheet, and the desired Merged sheet
I can do this manually as follows: Append the "New" rows to the "Original" rows, and sort by "Acronym" (C-Column). Then delete adjacent rows that have the same "Acronym" to avoid duplicates. How can I do this without this manual (and error-prone process)?

<<EDIT>> Hmm, I better have the font of the new entries in the merged sheet color-coded so that the person I deliver the document to can readily understand the additions.

The table is sorted by Term (but that is not the most essential point, since once the unsorted merge is done, I can sort the B-Column).

Since I am on a closed network, I cannot add plug-ins, and if this requires a script vs. using Excel wizards, then I'll have to type the script in.

Thanks,
Paul
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.