Spreadsheets

6K

Solutions

24

Articles & Videos

5K

Contributors

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 had this question after viewing Produce the count value next to each filterable item in a spreadsheet.

I am using this macro with data from another sheet and the results are cut-off due to all the data not fitting in dialog.

If possible, the results dialog should automatically expand as necessary to fit all the text to the right, and be in a smaller font size.
CountItemsv2.xlsm
0
Free Tool: Subnet Calculator
LVL 8
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

hi,

I sometimes use google sheets as a simple database, and would like the option to edit in form view.

I expect a crude setup is possible using google scripts, but I have not yet delved into this as I'm new to google scripting.  I would like to get some tips on getting this up & running. There maybe a few steps involved in which case I'll post aside questions if needed.

Here is a template which I'll aim to get working so if successful this could be the answer:

short url: bit.do/googlesheetformedit
redirects to: https://docs.google.com/spreadsheets/d/1vyC3yaB1cKegdRHeXeNvgr8N06S7OzUw2EPnY3fEOVE

2 main stages:
1. generate the query output
2. create a trigger to edit source data when the column "value" is changed

There may already be a google app that does this.

Thanks
0
I have an excel sheet with 6 different room types in a hotel. These room different items and quantities of those items in each room. One room may have 2 lamps, where as the other may have 1. I need to insert the aggregate data into 1 table in order to make an accurate budget of all the items that I will order.
Hotel-1.xlsx
0
In Excel 2007, how do I change for only one cell from dot to comma as separator for decimal value?

I want to keep the global language setting to English and keep global dot as decimal separator, and only change for this cell to a comma as decimal separator. Is that possible?
0
I am trying to strip the decimals from a negative number. In this instance it is -102.60055560 (a longitude) and everything I try Excel rounds it to -103. That of course would put it in a different county. I am just looking to have the -102 result, without rounding
0
I have a spreadsheet with data.  In column C I have sporting houses (Brown, Green, Blue, Red) and in column F I have the points.  I want to be able to have a running total of the points by house in four cells next to the data.  How would I do this?
0
I have for example line items with different quantity amounts and wanted the very first line item (row) to have the total and delete the rest of the rows.  In this case I have 1000's of line items.

Please See attachment
C--Users-lfreund-Desktop-QTY.xlsx
0
Here's a softball for you Excel experts: I want to extract the string between the dashes ("-"), e.g.:

"AC00710003 - Process Air Distribution - FS Review & Approve in AZDocs Pre-ACO" should return "Process Air Distribution"

What is the correct formula?
0
I have already make a "XML Maps" Mapping list under the XML Sources.
It works file when I do the XML import to the Excel sheet.

However, I would like to add the file name of the XML which imported to the excel.
Can anyone help me on this?

Enclosed with the Excel sheet with the mapping list included
https://drive.google.com/open?id=0B-cvVcO00sRvVldicS1kWFhGSGs
0
Hi there,

I need help putting together what might be a complex formula. I think I know the conceptual building blocks, but need that last bit of help to bring it all together - or alternatively be given a different approach. First some background.

I am using pwrSIMILARITY to calculate textual similarity score between any two given input cells, eg.:

=pwrSIMILARITY(M292,N292)

Open in new window


Great! However what I am trying to do is calculate multiple comparisons. The spreadsheet I have has multiple columns with similar text. Some of these columns might be blank. For example, out of a possible 17 columns for any given row, 3 may have non-empty text.

pwrSIMILARITY is symmetrical in that the order of input cells produces the same result. I can leverage this in a basic permutation calculation. For example, for 3 input columns, (say C1 to C3) there are just 3 'paired' calculations to perform:

=pwrSIMILARITY(C1,C2)
=pwrSIMILARITY(C2,C3)
=pwrSIMILARITY(C3,C1)

Open in new window


I can then take the average of those to roughly produce a score which I interpret as the overall similarity score for text across columns C1 to C3.

I can use COUNTA and PERMUT to work out the number of pair calculations as follows:

U1=COUNTA(D292:T292) //equals 3
V1=PERMUT(U1,2)/2 //equals 3

Open in new window


The questions are:

How do I get a reference to the cells that COUNTA …
0
On Demand Webinar: Networking for the Cloud Era
LVL 8
On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

please open attached file.

i have two tables and i need to merge them together by not showing duplicates and also sort ascending.

i have provided example on how the end result should look like.

there are some state capitals colored in red with either of the tables which also should be removed.
EE.xlsx
0
I have a spreadsheet (see attached) with a planner number in column A and a date (already in chronological order by planner) in column B. I want a formula that will count the number of lines for the same planner that are: 1) older than TODAY, and 2) equals TODAY.

Ideally, I'd like a VBA script that would look at the spreadsheet (which changes every day in terms of planners listed and the number of lines, in terms of the number of planners and the number of lines per planner for a given day) and examine however many lines are in the entire sheet for the occurrences defined in the first paragraph above, and provide by planner number the total number of lines prior to TODAY and the number of lines equal to TODAY.
Potential-Totals-by-Analyst.xls
0
Team,

i would like your clarification for the  following . Template i have attached for your reference

Clarification  1 :

Is it possible , that i can have the whole row colored, if i select particular department such as IT
ie, if i select D3 as IT , then the row A2:D3 will be colored .

Clarification 2 :
If i color individually TAG# cells, can i filter cells only by color . Objective is to followup on the cells at the later stage .

Highly appreciate your support

Regards,
Sid
template.xlsx
0
Column "A" with has a named range of "WONumbers" and over to column "H" that has priorities, and column "S" that has dates and empty cells. I tried this:

=COUNTIF(AND(OFFSET(WONumbers,0,7),"=1",OFFSET(WONumbers,0,18),">0"))

It just gives me errors.
0
I have code (below) which will compare the values of one row with the values of the next two rows. It then tells me how many of the values in rows two and three match any of the values in row one.

=SUMPRODUCT(COUNTIF(B6:G7,B5:G5))

The problem I have now is that I only need to know IF a value has been repeated and not how many times the same value is repeated.

for example,

1 2 3 4 5 6
1 2 2 3 0 0
1 1 2 0 0 0
here, the code above, would tell me how many numbers from rows 2 + 3 match the numbers from row one,
so count (3x1)+(3x2)+(1x3) = 7 matches.

What I need the code to tell me is that 3 numbers from rows 2 + 3 match 3 of the number in row , so the count should be 3.

Can anyone help me with this code please ?
0
WHEN I WRITE IT DOWN
Untitled-picture.pngrange1.xlsx
0
Hey everyone,

Asked this question recently and got an answer that worked but I have a new problem that requires a new solution.

Example File Attached.

Basically the Attainment percentage will yield a % Payout Factor, not every value is listed in the attainment/payout factor chart so the formula has to produce a % Payout Factor that's aligned with the chart giving a result that makes sense when the Attainment doesn't appear directly on the chart.

Example, if Goal Attainment Chart has a 100% attainment which results in 100% payout factor, and 105% attainment that gives a 141.7% payout, if the employee achieves 101% attainment their payout factor should be between 100-141.7% but not just an average, it has to be closer to the 100%'s payout factor, maybe around 110%?

If anyone has a solution, it'd be greatly appreciated.

Thank you,

- Rick
Example.xlsx
0
Hi,
Having trouble configuring formula to round a range of values to one.
Please see attached
Thanks
Ian
round-to-one.xlsx
0
We use a custom format (Format Cells-->Number-->Custom) that is 0000-00-00. Generally, entering 1234 will result in 1234-00-00 in the cell. HOWEVER, occasionally I will have a spreadsheet that 1234 results in 0000-12-34 in the cell. I know this is the result of a setting in Options, but I have spent an hour comparing options in two spreadsheets, one that returns 1234-00-00 and the other which return 0000-12-34 and can find no difference in the options settings. Any ideas?
0
Enroll in June's Course of the Month
LVL 8
Enroll in June's Course of the Month

June's Course of the Month is now available! Every 10 seconds, a consumer gets hit with ransomware. Refresh your knowledge of ransomware best practices by enrolling in this month's complimentary course for Premium Members, Team Accounts, and Qualified Experts.

I am just learning to work with formulas in Excel tables. The following formula that I created works, but seems clunky having to duplicate the range criteria, and I have not been able to successfully streamline it:

=SUMIF(Table1[WO1],$A2,Table1[WO1_Process_Min])+SUMIF(Table1[WO1],$A2,Table1[WO1_RWK_Min])

My attempts have caused errors in the sum_range:

=SUMIF(Table1[WO1],$A299,Table1[@[WO1_Process_Min]],[@[WO1_RWK_Min]])

Is it possible to simplify the formula so that SUMIF only has to be entered once?

Thanks,
Andrea
0
Hi everyone,

Basically I have an attainment percentage that must retrieve a payout percentage (0-250% Capped). Typically, whenever I've created these formulas before I could just create a giant table with every metric and use a VLOOKUP or a long IF function but that's not the case for this document. I need a formula that can find in between values on the attainment column and determine an in between value for the payout column.

Example document attached with my current findings based on IF statements and a column I created with what the outputs should look like with a working formula. Table included as well.

Really appreciate the help,

- Rick
example.xlsx
0
Is there way to select a certain range of cells and get it to auto detect either a number or phrase and add all there values that contain that word or phrase? For Example there are 2 lines with Product Number - xxxx, and i want to add all there values to one cell. Is this possible?
0
When shipping our work week is considered Tuesday through Saturday due to packages moving via parcel which only deliver during these days.    We are attempting to calculate the number of days from release until the package is delivered to our customers so for an example:    The package left our site on 3/16/2017 and based upon notification from our parcel carrier it was delivered to our customer on 3/17/2017 which is 1 day.......BUT we have this situation where the package left our site on 3/17/2017 and it was delivered on 3/18/2017 but the calculated date was 0 days since Saturday was a "non work day" when using the networkdays option within Excel.       Is there a way that we can define, within a formula, a workweek of Tuesday through Saturday and if necessary exclude holidays?

Thank you
Rosemary
0
I have Excel 2016. New Optiplex 3050. i5-7500. 8GB memory. Samsung SSD. Windows 10 V1703. Excel itself opens almost instantaneously. But when I double click on a spreadsheet on my desktop it takes a good 20-45 seconds to open. Small, small spreadsheets. I don't worry about it because I don't use Excel that much and my Desktop is redirected to a server but...

Today I put together a new computer for a Customer. New Optiplex 3050. i5-7500. 8GB memory. Samsung SDD. Windows 10 V1703. Excel 2013. His Desktop is not redirected but his Excel works just like mine. Excel itself open instantly but when I double click on a spreadsheet on his desktop it takes a good 30-60 seconds to open.

Something isn't right. It just shouldn't take that long to open a spreadsheet. Any ideas?
0
Hi Team, am trying to find a solution for a query which am facing. My query is am trying to extract data from a huge pivot where we have used multiple filters. I need to extract the subtotal from pivot for different Regions like Americas, Europe, India etc as per Bench cost, Vacation, Bus development etc. I also need data as per designation like Manager, consultant, like how much is the bench cost at a manager level. I tried using Index and Match, but getting an error. Can someone help please. Am enclosing a sample. Please note, i cant share the whole pivot due to privacy concern, but need a formula which can work well even with multiple filters on the pivot.
sample.xls
0

Spreadsheets

6K

Solutions

24

Articles & Videos

5K

Contributors

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.