How if and search function together put values false as 0 or blank instead of #value! in cell.

pankajgrover

Board Regular
Joined
Oct 27, 2022
Messages
95
Office Version
  1. 365
Platform
  1. Windows
Hi, I am a very normal home basic user . Looking for 1 little help . Here attached excel, In Worksheet Test, R4 displaying #value! how can i calculate if result is false, then put a value like 0 or blank instead of #value!.
Actually I am trying to matching name with cell name , if exists i want some value divide some cells otherwise if not match name, result should be zero or blank.
Please help me with the formula. Thanks
 

Attachments

  • Screenshot (1).png
    Screenshot (1).png
    98.6 KB · Views: 8

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
81,649
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
How about
Excel Formula:
=IF(ISNUMBER(SEARCH($R$2,C4)),O4/Q4,"")
 
Upvote 0
Solution

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
81,649
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 
Upvote 0

pankajgrover

Board Regular
Joined
Oct 27, 2022
Messages
95
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
How about
Excel Formula:
=IF(ISNUMBER(SEARCH($R$2,C4)),O4/Q4,"")
As i apply your formula, it gives value blank as i want. But cell fill green color instead of grey. I already put condition formatting in cells that if cell value more than 0 then Green if less than 0 or - then red color. But if blank after applied your formula how can i give color to Grey, because this cell has no value ? Thanks
 
Upvote 0

pankajgrover

Board Regular
Joined
Oct 27, 2022
Messages
95
Office Version
  1. 365
Platform
  1. Windows
In that case change the "" at the end of the formula to 0
Also i found another solution and that is i go conditional formatting and i select cells condition if cells have blank put color fill is "Grey" and it works with "" also. Thanks for your good support and i get the solution exactly i just wants.:)(y)
 
Upvote 0

pankajgrover

Board Regular
Joined
Oct 27, 2022
Messages
95
Office Version
  1. 365
Platform
  1. Windows
Hi @Fluff here is screenshot attached. in Q5 i am trying to count words of C5, but when i drag fill it counts also blank value and fill 1 value . how can make it to as blank in Q5.
I am using this =LEN(TRIM(C5))-LEN(SUBSTITUTE(C5," ",""))+1 but it gives blank column value as 1 instead of blank. Thanks
 

Attachments

  • 2022-10-28.png
    2022-10-28.png
    37.2 KB · Views: 2
Upvote 0

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
81,649
Office Version
  1. 365
Platform
  1. Windows
As this is a totally different question it needs a new thread. Thanks
 
Upvote 0

Forum statistics

Threads
1,186,773
Messages
5,959,698
Members
438,441
Latest member
SnoopRussyRuss

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
Top