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: SSL Checker
LVL 8
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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 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
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
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
Free Tool: Path Explorer
LVL 8
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

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

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

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
Hi, me again. I was asking something very similar to this a couple of days ago. Here goes again! In the spreadsheet attached, I have project names and basically when they started (indicated by a 1, the zero indicates when the project ended). I dealt with the project end date by a wonderful solution posted on here a couple of days ago using Match and Index. Here is what I need now: I need to know when a number 1 appeared in the cell, and then I want the date above in row B to appear in the cell. For example: the project in cell F19 did not become active until Feb 4, 2014 (as indicated by the 1 in the cell). Can you please help me write a formula that will automatically tell me the date. The last formula I used (Thank you for the solution!!!!!!) was: =IFERROR(INDEX(D$1:AS$1, MATCH(0, D3:AS3, 0)), MAX(D$1:AS$1)). This error checks is the cell has a #N/A in it. Thank you.
SnapShotExcel.png
0
I have information about students, forename, surname, funding, course, start and end date. So I have my own sheet and I want to compare it to a college excel sheet how do I do that quickly.
0
Hi,

In excel how to get the highest letter in a column/line. For ex. in the following exemple A1:A5 the highest letter is "D".

  | A  |  B  |
------------------------
1| A |
2| B |
3| A |
4| D|
5| A |

best regards
0
I wanted to calculate work days from two columns:

If there is a date in column A and no date in Column B then populate Column C + 1 Day from that date in Column A, then Column D + 3 Days from Column C and Column E + 6 days from Column D.  Workdays

But If there is a date in column B and a date in Column A then populate Column C + 1 Day from that date in Column B, then Column D + 3 Days from Column C and Column E + 6 days from Column D.  Workdays

Essentially between columns A and B dates will be entered in one of these columns but I wanted to take the latest date to populate Columns C,D, and E when a date eventually gets entered in both spaces.

See attached example, I added some notes to better illustrate what I was looking for in a formula.
0
Hi,

Can someone please help me with combining both the function together.

VLOOKUP(U3,Sheet2!C:D,2,0)  and =IF(T3<$P$1,"Yes",IF(T3>$P$1,"No"))

if vlookup gives #NA than IF function applies

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