# value error

1. ### Referencing values from inside & outside a table in excel

I have a table that gets updated based on a couple of data sources that are manipulated in the query editor in excel. That data is referenced by another tab for a process in my company. The table will fluctuate in # of rows, but shouldn't exceed 400. In the tab that my company uses, I want to...
2. ### Subtract constant in sum function

Hi All, I have a small problem. I need to sum a range of numbers and subtract a constant. I have used the method: {=SUM(B4:BUU4-0.5)}, which gives me a #VALUE error. The problem occurs since there is empty cells in this range. I have also tried with a {=SUM.IF(B4:BUU4,">0",B4:BUU4-0,5)}, but...
3. ### Correlation between two columns, ignoring errors

Hi all, I want to check the correlation between two columns (column B and I), taking out all the values that give an error. Furthermore if a certain nth value in column B (I) gives an error, the corresponding nth value in column I (B) should also be ignored. I did it this way but it gaves a...
4. ### Weird UDF problem

Hello guys I wasn't expecting to post a question here, but here it is: related to another post here (https://www.mrexcel.com/forum/excel-questions/1090639-formatting-cell-value-based-another-cells-colour.html) I decided to make a small UDF to determine the color of a cell. At first it all went...
5. ### Array formula #VALUE error

Hi All I am creating a formula but am having an issue which im not sure how to correct. My formula is below: =TEXTJOIN(", ", TRUE, IF(((MONTH(A1) >= MONTH(Sheet1!B2:B26)) * (MONTH(A1) <= MONTH(Sheet1!C2:C26)) + ((MONTH(A1) >= MONTH(Sheet1!D2:D26)) * (MONTH(A1) <= MONTH(Sheet1!E2:E26))))...
6. ### Value Error when activating content/updating content from other workbooks

Hi! I was tasked to create a relatively simple series of excel workbooks that connect to a main workbook. I have several different districts that are gonna have their own budget workbook aswell as a report workbook from which the person in charge of that district can send out a small report...
7. ### IF Function that has multiple criteria.

Hi there, I am trying to construct an IF Function for the following problem: Data: 5 collums of data (A,B,C,D,E) and 33 participants. Conditions: define person as 'high risk' if either '3 out of 4 values (A,B,C,D) are smaller than 10' OR if '2 out of 4 values (A,B,C,D) are smaller than...
8. ### SUMPRODUCT #VALUE! error

I can't figure out why I'm getting this error only on this one row. All my other formulas on the Revenue Summary sheet work fine. {=SUMPRODUCT((MONTH(InventoryTracking[Date Sold])='Revenue Summary'!M2)*(InventoryTracking[Sale Price (Before Tax)]))} **no problems with this one**...
9. ### VALUE error - Cannot pass vlookup result to array items

Hi, I'm trying to create a keyword search function, that uses a named range called "kwmap". It creates a string array from the found keywords and maps them with vlookup to the corresponding number (solution ID). After that it should compare the amounts of each ID and set the result to the ID...
10. ### Vlookup Error

Hi so I have a reference issue with my Vlookup and I'm not sure why. All the data seems to be matching and the format of the cells match up. If you look at sheet 1 column B should be looking up and copying information from Sheet 4 which should then have sheet 1 column E pull information from...
11. ### Help: formula not evaluating intuitively

Good morning! I did a bit of searching to solve this on my own but alas, I have been unable to. I am using a formula to determine how many of these characters exist in a cell: =SUMPRODUCT(--ISNUMBER(SEARCH(Sheet2!\$A\$1:\$B\$28,Sheet1!H2))) The formula is by default evaluating to 3 and I don't...
12. ### COUNTIF Over Multiple Sheets

Hi, I am trying to write a formula to count the number 1 over a set of cells in each sheet into a totals sheet at the end. I have named a sheet First and Last at the relevant places to help with the formula but get a #VALUE! error when I try and get the result...
13. ### Difficulties with For... Next Loop

I am trying to write UDF in VBA that will give me a the y-coordinates to confidence bands for a linear regression of data. I am new to programming with VBA but I have scoured the internet in order to try and figure out what I'm doing wrong, but I cannot figure it out. I basically can't get the...
14. ### Rectifying Value Error in Excel to find percentage by extracting dividend/divisor from a single cell having variable length.

Hi there! I am having a problem in finding percentage. In cell B3 I have a text-number string like "Science580/1050", "Arts550/1050", etc respectively. To find percentage by dividing 580 by 1050 I developed a formula copied below. Please note that in above example i.e. "Science580/1050"...
15. ### Error Message on Formula returning #Value

Looking for help on a relatively simple (i thought) formula for doing two things in one cell. I have a 2 sheet workbook. On Sheet 1 i have two cells (A1 and A2) with NAMES OF EMPLOYEES in them. I want to transfer that info from Sheet 1 to Sheet 2 to let's say Cell A50 on Sheet 2 but with the...
16. ### RankIF Large Set OF Data - Need Formula!

Hi Everyone, I have a large set of data in my excel spreadsheet with 9800 rows. I want use RANKIF function by comparing two columns. when I am trying to solve the problem using the below function =SUMPRODUCT(--(H2=\$H\$2:\$H\$9692),--(AZ2<\$AZ\$2:\$AZ\$9692))+1 it shows me a value error. I think due...
17. ### Issue with SUMIFS/COUNTIFS formula throwing VALUE errors...

Hey all, So I have a few formulas that run in the background of a workbook that is used at my work and keeps track of progress/amount of work done by different people in my department. I am trying to make some tweaks/additional adjustments to the formula, and it's getting hung up but I can't...
18. ### Can't get excel to recognize numbers and not text in a cell

It is probably a simple answer that i just have not been able to see. I have converted everything in my spread sheet to inches, then did the formula to find square ft. - No Problems there What i need to do is " lets say A1 = 66 and A2 = 66 The Sq. Ft. it gives me is A3 = 30.25. I need to...
19. ### Value error in one IF formula but not another

I have a workbook that has two sheets. Sheet one is in color and there are six timecards on it that all fit on one screen. Sheet 2 has those same six time cards, organized vertically instead of horizontally, and all the color formatting has been removed for printing. The idea is that everyone...
20. ### Counting January dates with sumproduct from cells populated with if statement “” evaluates #value!

Excel 2007. I have a workbook with three main sections that share information. 1. Project sheets. These have random sheet names and contain project information. In cell S16 I am calculating a start date based on project type and status. I am checking to see if cell 108 is filled in to...

### This Week's Hot Topics

• separating multiple Proper names (uppercase letters) from a list
I have a list of proper names that only has spaces between their first and last names. I need a formula that can separate these names into...
• 'for' Loop
Hello guys, I am trying to do something new. With the help of a VBA code, in every case, last 2 Values of last 2 cells of columns D-15 to D-28 to...
• Open specific pdf in folder with vba
Hi, Below is the code in use , unable to find rich icon to put code inside using a mobile. The code in use is shown below. Everything works as it...
• What is wrong with this For Loop code?
I am trying to loop through each cell in Column U from U4 to last row to check if the percentage in the cell is greater than -.050%. In a...
• Data to match
Hi there, I have created 2 worksheets with data. First is all materials used and second has the bottle type used for each of material. How can I...
• Shifting Columns
Hi Dear Community, I'm trying to find the best way to Shift (Cut - Paste) all the information below and to the right of the "Worker" cell in VBA...

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back