ignore

  1. R

    Ignoring the value of a formula in a cell.

    Another one I'm not sure how to explain. So basically, I'm trying to make a spreadsheet that determines the last cell in a column that has a calculated value from a formula. So, the value in G5 is based on subtracting the previous value in G4 from the Value in C5 using: =IF(C5=0,"...
  2. U

    Anyway ignore excel error about + or = or - sign that written at first cell

    Hi guys anyway ignore or terminate this error and write + or - at first cell as a text?
  3. J

    Conditional formatting to highlight duplicate and ignore value x in same column

    Hello, I'm trying to figure out how I can highlight cells that contain duplicate values, but only if they are in the same column. Obviously, I could just go column by column and add a conditional formatting rule for each, but I'd like to be able to use one rule that applies to my entire range...
  4. J

    Formula for counting

    I am trying to achieve the results in D3. I'm in need of a formula that will count unique names, ignoring the blanks and duplicates. Also ignoring the word "na" and "ns"
  5. J

    counting, ignoring multiple words and blanks

    I need formulas that will count unique occurrences, certain words while Ignoring blank cells and duplicates. I am trying to achieve the results in cells D8 and D9. In this case, I need to count occurrences that starts with W81UTH excluding duplicates and blank cells. I also need to count ALL...
  6. J

    Formula for Unique Occurence using multiple columns

    I am trying to achieved the desired results on E4. I'm looking for a formula that will count a unique occurrence specific to a date, ignoring duplicates and blanks. In this case, 19NOV19 has 2 trucks. 20NOV19 has 3 trucks. 21NOV19 has 2 trucks.
  7. J

    Formula help, counting unique occurences.

    I am looking for a formula that will count unique occurrences that start with W81UTH, ignoring duplicates and blank cells. Also looking for a formula that will count unique occurrences that does NOT start with W81UTH, ignoring duplicates and blank cells. I want to achieve the desired results...
  8. D

    Remove characters in formula

    I need to run this formula but need it to ignore the text that is in cell W12. Formula is =sum(w12*t12)/100 Cell w12 reads as ‘15 kestrel’ So I want to ignore everything but the 15, I have other cells with different text so it has to ignore generic text.
  9. D

    How to ignore #DIV/0! error

    Array formula is {=AVERAGE(IF(ISNUMBER(MATCH(I13:I18,SMALL(I13:I18,{1}),0)),(H13:H18)))}. Want to ignore the #DIV/0! error and use the cell in column H corresponding to the I column having the #DIV/0! error. TIA
  10. C

    Conditional SUMPRODUCT to also ignore blanks

    Hi, I have the following code which calculates a weighted average: =SUMPRODUCT(--($U$2:$U$1203=AE3),$AB$2:$AB$1203,$C$2:$C$1203)/SUMIF($U$2:$U$1203,AE3,$C$2:$C$1203) It works very well except that I would like it to ignore blank cells in the range $AB$2:$AB$1203 I tried to change it to the...
  11. sharky12345

    Highlight cells less than 100% but ignore blanks

    I'm trying to use the conditional formatting option to highlight cells where the value is less than 100%, (the cell values are percentages), but I need to ignore any cells that are blank. Can anyone help me please?
  12. most

    How to solve a issue with three criterias

    Hi, I would like some input/pointers on how to solve this, I'm not sure where to begin... I want to write Active, Not active or Ignore in column E based on three criteria. As you can see in the table, a person can occur 2-3 times, even 6 times in my orginal data. Data is sorted on column A. If...
  13. R

    IF Function ignoring NA

    Hello guys, I am currently processing a huge dataset. My question is that how can I ignore NA when I want to run and repeat this function in excel. =IF(AND(RankLN!D45>0.3,RankLN!D45<=0.7),"M",IF(RankLN!D45>0.7,"W","L")) In detail, for example, I have a column, we say column D. In this column...
  14. R

    If function ignoring NA

    Hello guys, I have a column of numbers and some NA. I am creating dummy variables, for example, IF(A2>0.7,1,0) and repeat this function into the entire column. However, I have NA in this column. Therefore, how can I ignore NA and set the output of NA as 'NO'. Thank you so much.
  15. C

    Data Validation - Ignore Blanks Not Working?

    Good evening, I'm trying to create a drop-down that is essentially generated by an array formula. The range is 15 cells, but the list often has less than 15 values. Is there a way to get the drop-down menu to not have a bunch of blank white space at the bottom with a scrollbar for lists that...
  16. A

    VBA to Highlight entire row with conditions

    Hi, I am looking for VBA to highlight the entire rows which satisfy any one of the following conditions for active sheet. 1. IF Column J blank and Column R has some data then Highlight(vbyellow) 2. If Column P and Column R both are ‘0’ and column J is ‘A’ then highlight (vbyellow)...
  17. D

    MS Excel Spell Check not working properly

    I am using MS Excel 2016 and the spell check will not allow me to select 'Add to Dictionary', 'Ignore Once', 'Ignore All', etc. The buttons are live, yet no response when I click them. The only button that seems to function is the cancel button. Any help on this is appreciated. Thank you.
  18. D

    If Cell Blank ignore, otherwise Concatenate

    Hi Guys, Wondering if you could assist me with this simple formula - I'm trying to combine, "column E" with "column D" with a "." in between, I did the first one as an example - "BQ6924-001" add the "." and then the "3.5UK" so we get "BQ6924-001.3.5UK" I want to ignore the blanks though in...
  19. P

    Count but ignore duplicate

    Guys I have this below table where I want to count the number of occurence of each individual but ignore the duplicate instances Dates. Names 1. X 1. G 2. X 2. X 3. G 4. X 5. G 5. G Please help me, it's urgent
  20. S

    Ignore blanks formula

    Hello, Please may someone help me. I have a number of tabs in my spreadsheet, some have datapopulated, others don’t. I have an overview tab that brings everything together. I am simply using the formula: =tab!cell However this is bring in a ‘0’ when there is no data (thecell is blank), does...

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top