# value error

1. ### 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...
2. ### 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...
3. ### 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...
4. ### 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))))...
5. ### 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...
6. ### 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...
7. ### 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**...
8. ### 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...
9. ### 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...
10. ### 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...
11. ### 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...
12. ### 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...
13. ### 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"...
14. ### 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...
15. ### 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...
16. ### 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...
17. ### 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...
18. ### 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...
19. ### 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...
20. ### SUM if for separate sheet

Hi people, I a new here and i have a worksheet with several buyers in sheets. I need to hv one sheet in which i have for evry byer how much certai goods did he take. Crteria is month, type of goods. I am stuck on SUM IF formula which is giving zero or VALUE error Here is my formula...

### This Week's Hot Topics

• VBA (Userform)
Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
• List box that changes fill color
Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
• Remove duplicates and retain one. Cross-linked cases
Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
• VBA Copy and Paste With Duplicates
Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
• Macro
is it possible for a macro to run if the active cell value is different to the value above it
• IF DATE and TIME
I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...