Using if function of MS excel 2007 but not working properly.

Question

0.00/5 (No votes)

See more:

Excel-2016

In the Excel Sheet I'm using If function but not working properly, What I'm trying is in my sheet there are three columns heading as Column A, Column B and Column C in Column A Cell A1 to Cell A10 are filled and in Cell B1 user will provide some number which will be carried out by other cells from B2 to B10 using =$B$1 formula now what I want is that in Cell B2 I want to put this formula =If(A1="",0,B2=$B$1), means if cell A1 is blank then cell B2 will return 0 and if cell A1 contains data then it it will fetch the value of Cell B1, But this formula only returns 0 and not fetching the value of cell B1. Please someone help to solve it. If there is any wrong in the formula so please guide me proper function to solve.

What I have tried:

what I want is that in Cell B2 I want to put this formula =If(A1="",0,B2=$B$1), means if cell A1 is blank then cell B2 will return 0 and if cell A1 contains data then it it will fetch the value of Cell B1

Posted 25-Dec-21 22:33pm

Md M Ahmad

Updated 29-Dec-21 10:14am

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2021-12-25T23:53:00

Take out the comparison:

=IF(A1="",0,$B$1)

With your version:

=IF(A1="",0,B2=$B$1)

It's trying to fetch the current value of cell B2 and compare it with $B$1 in order to modify cell B2 - with is a circular reference because the assignment to B2 would change the value in teh comparison, which would change the assignment, which ...

And BTW: I know you are a Help Vampire (your record of Q vs A shows that) but this isn't an Excel help forum: in future these queries should be directed to a site that is.

Patrice T · Answer 2 · 2021-12-26T06:55:00

Quote:
Please someone help to solve it. If there is any wrong in the formula so please guide me proper function to solve.

Learn to use Excel integrated helping tools.
Ribbon 'Formula' > 'Formula Audit' Block > 'Formula Evaluate' : this tool will show you how Excel avalate the formula step by step, it helps you to understand where the formula goes wrong. Great learning tool.

Quote:
=If(A1="",0,B2=$B$1)

As far as I understand your problem, you should try:

VB

=If(A1="",0,$B$1)

[Update]
Have a look at this tool: FormulaDesk - FormulaSpy[^]

Maciej Los · Answer 3 · 2021-12-29T10:14:00

Solution 3

In a short, this part of your formula: B2=$B$1 evaluates into boolean value and returns:
- true - if B2=$B$1
- false - if B2<>$B$1

So, the result of formula: =If(A1="",0,B2=$B$1) should be true/false if A1 cell contains data... :)

Got it?

Posted 29-Dec-21 10:14am

Maciej Los

Using if function of MS excel 2007 but not working properly.

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0