1. A

    Sumproduct large with adjustable reference range based on another cell

    Hi, Is it possible to change the reference range in a sumproduct large formula to expand based on a value in anther cell? I'm using this formula: SUMPRODUCT(LARGE((A2:A9=G2)*(B2:B9),1)) and would like the range to expand based on value in another cell, say E1. If E1 was 20, than I would like...
  2. R

    Spreadsheet incredibly slow when moving tabs

    Hi I realize the title is not that helpful I have a relatively large spreadsheet - around 7 MB - with a lots of calculations and 50+ tabs It calculates fine and very quickly. However, my problem is that if I move a tab or insert a blank line (even if that line does not impact any...
  3. S

    Excel Operating on a PC

    Where can I go to find out why excel takes so long to save a large file, continually bogs down while performing macros etc. I use several different large spreadsheets with lots of calculations going on, they run and save quite well on my PC but bogs down on my assistants PC. Here is one error...
  4. C

    Excel Formulas to generate random values adding upto 100

    Hi Experts, Need your help in this. My boss wants me to create values for a group of materials bifurcated on basis of pack x Channel, now the values for a pack/channel combination should sum up to 100. <tbody> Unit Brand Channel Pack Value% EAST Product 1 Insti Medium EAST Product 1...
  5. M

    How to make every number in a range of cells unique???

    Hi, I have a large range of data - I need to use range formulas using If and Large combinations to return the largest number when certain conditions are met. As the dataset is large, there are duplicate values. As a workaround I add a very small number to every number in the range, to create...
  6. R

    Solution Appreciated

    Extremely large data set that will include the following information. Is there a simple method (formula) to provide the following summation (for a large data set,A1:A200, etc.)? <colgroup><col width="99" style="width: 74pt; mso-width-source: userset; mso-width-alt: 3620;"> <col width="64"...
  7. O

    Looking for an If formula

    I am looking to use a IF formula but not sure how to write it so it returns the value i want. I have a list of SKU's that have a location by them, i also have a separate list of locations. I want the If formula to return a value of 1, if the SKU's location appears in my 2nd list...
  8. C

    Index and match with large function

    Hi, I am using the Large function to calculate the rank and Index and Match to return the user name of that rank. Some of the ranks have similar values and then returns only the first name of that rank value. I would like it to return the correct user name for each rank value.
  9. W

    'Match' a range, skip a predefined cell

    I am trying to find where the top 3 values in a range are stored. That part is done, however, if one or more of the top 3 have the same value, I only get the same cell locations and not the top 3. Here is how my data looks like: <tbody> A B C D E F G H I J <tbody> 0.22 </tbody> <tbody>...
  10. R

    Rank and Scores

    Hi, I´m fighting against an elusive formula to assign scores, I have tried to use arrays and combinations of functions seen in old posts... with no positive results. I have a long list of rows of concepts, each of them related to several values that appear in columns, so they must be compared...
  11. W

    Uploaded screeenshots appear massive, even to me!

    I sometimes take screenshots on my 4K monitor and upload them to Imgur so I can embed them. Any ideas why they come out so large? It's not a problem on Even when I view them on, they look big there also. Example: They seem to be getting scaled up by a factor of 2.5.
  12. A

    Large IF OR

    Hello I have tried with Large IF OR, but it creates a boolean and Large dont care about that. I also tried with Sumproduct, however that only seems to function with 2 criterias that isnt OR but rather AND. To simplify my question i will ask with the alphabet. I have clipped my data in below...
  13. D

    IF AND Not working correctly

    Hello In Column Meter Type I have the following formula =IF(AA2="LARGE",AND(AC2={"BASIC","MRIM"}),"Meter Requires Upgrade") What I am trying to say is that if Column Classification contains the value "LARGE" AND column Meter Installation Code Contains the value "BASIC" OR "MRIM" then mark...
  14. M

    LARGE Formula & Return value from MATCH

    I have the below Formula using LARGE and right now it returns the largest value in the column, but the Column is a UID field and I am just using it to determine unique value, I want to somehow wrap this formula with and INDEX / MATCH array and first use LARGE to find the largest UID, then using...
  15. C

    Rename Multiple Worksheet Tab Names

    I need to add the text "ABC" to the end of some worksheet tabs that already have names. It's a couple hundred sheets in the middle of a large spreadsheet, so I cant use code that selects the entire workbook. Thanks
  16. M

    Refreshing web query creates #N/A in referencing cells

    Hi, I have a worksheet with one tab that has a web query retrieving data from an online table and another tab that uses a series of formulas that start by referencing the tab with the web queries. The order of the formulas is as follows: web query --> cells referencing the table created by...
  17. C

    Large, unstable ODBC file

    HI, How large are ODC files? I have one that is 3mb, even when I reduce it to a few lines. Itis slow and lumbering. Is this normal fo ODBC files? Thanks
  18. J

    LARGE IF OR question

    Hello, Currently I have the following formula which works great: {=LARGE(IF($Z$1:$Z$10000="EQUITY",$AA$1:$AA$10000),J23)} If I then wanted to include other candidates for large, with different text values than "EQUITY", all in column Z, how would I expand the above to include these?. Been...
  19. L

    Calculation bottleneck in a large financial model

    I built a fairly large and complicated financial model for valuation purpose, I’m now concerned with calculation speed. A couple of questions: Does table slows down calculation a lot? My data is kept in tables so that other formula referring to the data will auto update when adding deleting...
  20. D

    Conditional formatting

    HI all I have several large numbers 019332961003232531020025441319041221179549530 19332961003232531020021401319041221179549570 1933296100323253102002236131904122117954954 0193329610032325310200235813190411211795387001 933296100323253102002280131904112117953858...

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...