#value error

1. How to workout the error rate or percentage of a spreadsheet using a specific value as reference?

I want to be able to calculate the rate/percentage of error. I have 3 sheets: Master, Co-Master and ReDo. Both the Master and Co-Master sheets have a total time column (J) to give the total time a user has taken to complete a task. The ReDo sheet is solely to record issues with the Master or...
2. #VALUE when saved in OneDrive

I have a workbook that works perfectly when saved locally. Even with hidden data and "Protect Sheet" enabled, all is well. HOWEVER, when I save my workbook to OneDrive, and view the workbook online, all my functions return a #Value error. My worksheets have Review/Protect Sheet enabled...
3. excel If Function containing logical test from multiple dropdown list

Hey guys, I'm currently creating a room registry and encounter this error in one of my if function :error #value I tried to googling this issue but failed without luck. <tbody> type of room no.of night extra bed room price complimentary total </tbody> 1. under number of...
4. Sumproduct Error

Dear experts, Having gone around for an hour, I can't figure out why my Sumproduct doesn't work. =SUMPRODUCT(--('Balance Sheet_Property SS'!\$C\$14:\$C\$271=\$S\$32),--('Balance Sheet_Property SS'!\$D\$14:\$D\$271=\$S\$30),--('Balance Sheet_Property SS'!\$F\$14:\$F\$271=\$S\$33),--('Balance Sheet_Property...
5. #Value Error using IF Statement

Hi Community, I have a formula that is leaving me stuck and I'm not sure how to solve it and hoping you can help. =IF(ISERROR(D50/D49),"BCB Auto-Fail",D50/D49),IF(AND(\$C\$14,\$C\$18,\$C\$22,\$C\$26,\$C\$37,\$C\$40,\$C\$44)="---Select---","N/A") Issue: the formula should be trying to calculate that IF...
6. What is wrong with this formula?

Hi Super-Brainers Can you help me decide what is wrong with this formula? It is giving me #value error. =SUMPRODUCT(1*(\$D\$3:\$D\$354=\$A4),1*(MONTH(\$E\$3:\$E\$502)=\$D\$1)) Column D : Stages (e.g. Lead, Opportunity, Converted etc.) A4 = Lead Column E: Date corresponding to stage value from column D...
7. #Value, MODE, LEFT and time of day help, please.

Hello all - I have been given some great help recently in trying to return the MODE of a series of TIMES (as in times of day). This has been very helpful, and I thank you all. Now I have hit a snag in implementing it. In col C2 I have the following formula: =IF(Main!DG3="X",Main!G3,"") This...
8. IF formula shows #VALUE!

Hi guys, i have a formula which checks the value of a cell in the "Unique ID" columns against the "Unique ID" column in a different table. if its the same, I have it say Keep, if it doesnt exist, i have it so it says delete. however, a third value I'm getting is #value !. any idea why im getting...
9. Look-up combination of INDEX(array, (MATCH()) in a table returns #VALUE error

Hi all, I have two data sets (two sheets in the same workbook) where I need to locate entries from one in the other one. Both sets are formatted as a Table and I am using an INDEX(MATCH()) combination to match entries. If match is found, values from one of the fields in Dataset1 should appear...
10. help with extracting multiple array data

I have a project with a number of resources and different task to complete over a extended period. the maximum number of task assigned to an individual at one time is 7. I was planning to use a sum product which reference a drop down list of the resources,then task no, then date then tell me...
11. IF Function #VALUE Error

Okay, I am stumped (hence why I'm posting here). No matter what I do, I cannot get this formula to work. Here is the current bane of my existence: =IF(J7 < 25%, "Text 1"), IF(J7>25%, "Text 2"), IF(J7>50%, "Text 3"), IF(J7>75%, "Text 4"), IF(J7>= 99%,"Text 5") I have no idea what I'm doing...
12. #Value error when concatenating an alpha-numeric cell?

I'm trying to concatenate two columns of cells in Excel 2010. One is the name of a supplier, the other is a 5 digit account code. Some of the codes are fully numeric and some are alpha-numeric. I'm using this formula: =CONCATENATE(A2, - B2) in order to get a dash between the two parts. It is...
13. Creating an exception price list

Hey all I need some genius, I've been trying to find a way to swap an exception price with a generic item price for specific customers. I have two large databases, one with item codes/descriptions/prices (CC8-3465, CC16- 3324, MSO2-1456, etc.), and one with customers (1002 - Customer 1, 3244 -...
14. SUMPRODUCT working in argument window, but returns error in cell

Hi! I have two rows with values and I'm using sumproduct to subtract the two. However I also need the formula to ignore empty fields and don't do the substraction if the lower field is empty. It seems to work when i use the insert function window, but once the formula is applied to a cell it...
15. Filtering works manually but creates errors with VBA

I have a bit of a weird one so bear with me. I have an macro-enabled Excel spreadsheet (.XLSM) created in Excel 2010. On the first sheet (HoldsList) I have data formatted as a table called Holds (see below) with the autofilter turned on. What I'm trying to do is, using VBA, filter the table...
16. Very bad simple question for #value error

Hello All, I'm hoping that this is a simple question for everyone but it has absolutely stopped me in my tracks. I have a form that i am trying to pull data from a second page and I am getting #value errors when I use a formula to pull the data. I use ='CUSTOMER INFORMATION'!C12:G12 to pull...
17. Excel non contiguous named range values evaluate to #VALUE, causes SUMIF to fail

All of my named ranges refer to valid cell references, but the range Values come up as null (see Name Manager in photo). This causes the SUMIF function I wish to apply =SUMIF(RangeofID_Codes, ID_Code, RangeOfValues). I have tried evaluating the criteria against the source (budget codes on...
18. Problems with Countif and named range

So this is what I have for my named range: Public Sub Change_Range() ActiveWorkbook.Names.Add Name:="range_test", _ RefersTo:="'UK WWTWs'!\$H\$3:\$H\$" & Sheets("BusinessModel").Range("C47") End Sub And this allows me to change the end range of the range_test. And in C47 I have the value...
19. Calculating Number of Months Between Dates in Different Years

=(YEAR(B4)-YEAR(B3))*12+MONTH(B4)-MONTH(B3) B3--31/11/2013 <tbody> B4--31/03/2014 format dd/mm/yyyy I continually get a return of #VALUE. Please help, Tom </tbody>
20. SUMPRODUCT with Date Condition returns #value

Okay, So I am in my over my head again. I have the formula below that is returning a #value error. The part in red is the new condition I added. I thought I had it right. =SUMPRODUCT(('RAW DATA - PAGE CATEGORIES'!\$A\$2:\$C\$1048576=10/31/2014)* ('RAW DATA - PAGE...

This Week's Hot Topics

Hi guys i have sumproduct formula for counting two range of number, i want count active cells of formula that linked to another sheet...
• Block certain cells in condition met in cell A
Hi there, trying to figure out step by step how to build macros and learn more. Now given that my other code was a mess, I figured I would...
• IF FUNCTION FIRST, THEN WITH INDEX AND MATCH
Hi, Below formula works well, =(INDEX('PRICE LIST'!\$C\$7368:\$C\$7679,MATCH(1,(WORKSHEET!O28='PRICE LIST'!\$A\$7368:\$A\$7679)*(WORKSHEET!P28='PRICE...
• Match data from 3 columns to return data from the correct 4th column
Hi there! I'm trying to have a cell auto-populate the data in a cell based on the data entered in 3 other cells. I've pasted a copy of the...
• VLookup
Hi everyone, I need to find the value from one sheet to another. So in Sheet A Field N5 I have a value (Spark) I want to find Spark on the Sheet...
• Defining a range
Private Sub Worksheet_Calculate() Dim Xrg As Range Set Xrg = Range("K1") If Not Intersect(Xrg, Range("K1")) Is Nothing Then MsgBox...

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.

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