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 a formula to establish identical values within an index range.
Please see attached
Many thanks
Ian
identical-values-within-range.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.

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

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
I have a sheet with multiple column and want to convert into multiple rows.

Example:-

Input

Mat Plant1 Plant2 Plant3 Plant4
123 A           B         C          4
456 A           B


Expected ouptput

Mat  Plant
123  A
123  B
123  C
123  D
456  A
456  B

Thanks

Nitin
0
Hi,
I would like a combined match and lookup formula.
Please refer attachment
Many thanks
Ian
match-and-lookup.xlsx
0
A client of mine has a sheet full of Dynamic vLookup formulas like the following: =VLOOKUP("Value10",A$1:DD$800000,$K$1,FALSE)
The problem with this is it is very slow. Is there a different way to write his formulas that is faster?

Note: When the user updates K1, the vLookups update which columns they query as per above. He/She wants to retain this functionality.
0
Become a Microsoft Certified Solutions Expert
LVL 12
Become a Microsoft Certified Solutions Expert

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

What formula would I need to use to get only the most recent value for an  entry/entries from a list for a particular customer. Am attaching file with sample data and desired output...
EE_MaxIfs.xlsx
0
Hi,
I would like to add ^ (to the power of) to the following formula

=VLOOKUP(KA15,$DO$14:$DP$54,2,0)          ^B$15

Many thanks
Ian
0
I have a spreadsheet in LibreOffice Calc that includes an image at the top of every sheet. However, now there is no image but merely a pathname to the image which is no longer valid.

Is it possible to redirect this pathname so that it points to the new location of the image?

How do I embed the image so that it does not need the image to be held in a particular location?

Thank you
0
Hi Experts,

     I have a worksheet that consists of numbers in Column A, 100 rows to be exact.  What I'd like to do is have a formula that first looks at the numbers in column D, and then checks Column A to see if any one of those numbers are present.  If the formula detects any of the numbers that were noted in column D, it then notates what numbers it found in Column B.  I've attached an example of what I'm describing.  Any help with this is GREATLY APPRECIATED!
EXAMPLE.JPG
0
I'm getting a false reading when I should be getting an actual status and I can't figure out what's causing this.  Please see attachment.

Here is the formula at S2 that's giving me the false statement:

=IF(MAX(K2:N2)<TODAY(),"PAST DUE",IF(MIN(K2:N2)>TODAY(),"ON TIME",IF(AND(MAX(K2:L2)<TODAY(),MIN(M2:N2)>TODAY()),"LATE")))
C--Users-lfreund-Desktop-DATE---FAL.xlsx
0
Does anyone know what I need to change to get this VBA code to work in Excel for Mac? It works fine in Windows.

It is a show/hide toggle button for rows while the caption on the button changes as well. I used a form button (not ActiveX).

Sub HideDone()
Dim Shp As Shape
   
   Set Shp = ActiveSheet.Shapes(Application.Caller)
   If Shp.TextFrame.Characters.Text = "Hide Done" Then
      Shp.TextFrame.Characters.Text = "Show All"
      Range("A1").AutoFilter 10, "<>11. Published", xlAnd, "<>Cancelled"
   Else
      Shp.TextFrame.Characters.Text = "Hide Done"
      ActiveSheet.ShowAllData
   End If
End Sub

Open in new window


The message I get is
Run-time error 1004
Method 'TextFrame' of object 'Shape' failed

This code is highlighted in yellow
If Shp.TextFrame.Characters.Text = "Hide Done" Then
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.