IF function help

[flash33181]

I have a large spreadsheet I'm working on and here's what I want to do:

IF a particular cell has "x" value in it, then I want to multiply a corresponding cell with a pre-filled in dollar value by .45 and place that calculation in a different cell.

Example:

If cell A1="636" then multiply the value of cell B1 by .45 and place in cell C1.

Does that make sense what I'm asking? I can't figure out how to do it other than manually and I have hundreds of cells to do this on.

Thanks in advance.

 

[jeffreybrown]

Hello & Welcome to the board,

Why not in C1...

=IF(A1=636,B1*0.45,"")

 

[flash33181]

Hi, thanks for the welcome and the reply!

That's sort of what I thought the formula should be, but it returns a value of "FALSE" even in situations where the data produces a 'true' result.

Any clue why that might be happening?

 

[jeffreybrown]

Not so sure why...

Are all the numbers actually numbers and not text masquerading as numbers?

You could check by using =Isnumber(A1). Should return True if the 636 in A1 is a number.

Here is what I get in a quick test, but I can't duplicate your results.

=IF(A1=636,B1*0.45,"")

<TABLE style="WIDTH: 102pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=135><COLGROUP><COL style="WIDTH: 34pt; mso-width-source: userset; mso-width-alt: 1440" span=3 width=45><TBODY><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 34pt; HEIGHT: 15.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=21 width=45>636</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 34pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=45>10</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 34pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=45>4.5</TD></TR></TBODY></TABLE>

Have you tried to evaluate the formula to see if you can indentify where the error is coming from?

 

[flash33181]

The Format Cells option had all my "Numbers" set as "General" which, forgive me, I don't know if that makes a difference or not. But I set them all to "Number" just to try it out.

Now when I use =IF(A1=636,B1*0.45,"") it is still producing FALSE as the outcome.

It doesn't appear to be recognizing what is in cell A1 since it's producing FALSE
for everything even when '636' IS the value in the cell.

I tried opening a brand new workbook and entering some dummy data and using that formula and it works perfectly. So the formula isn't the problem. It's something with my sheet.

But other formulas I've tried (non-IFs) have worked just fine. Such as SUM for example...works like a charm.

 

[jeffreybrown]

So you are saying if A1 contains 636 and B1 contains 10 then =SUM(A1:B1) returns 646?

Have you manually type over the values in A1 and B1 to see if there could be any spaces or something strange in those cells.

I don't see why a Sum would work and not the if statement.

How does this formula evaluate?

=IF(AND(N(A1),A1=636),B1*0.45,"")

 

[flash33181]

I just tried manually retyping the A1 data and the original formula you supplied does in fact work.

I suppose either way I have to go through manually and make changes then.

 

[jeffreybrown]

Try this...

Type a 0 in some cell and copy

Highlight the range of cells in column A >> right click >> paste special >> Add >> OK

This will convert the cells to numbers. If this does not correct the problem, then maybe in another cell try...

=TRIM(A1)+0

and then copy down until the end of column A.

Now you would have to replace this new column in place of the old column A

 

[flash33181]

Perfect, that worked. Thanks so much for your help.

 

[LadyBills02]

Not sure what info you need, But I am making a guest list and I am trying to see if there is a formula to help. I have a column with #invited and then next to it is 4 columns with the age range (4 invited, 2 adult, 1 child age 0-4, 1 child age 5-11) is there a way to do an IF function to total the age column like IF #invited>0 then add number in age column and total at the bottom.

#invited, Adult, 0-4, 5-11, 12-20
4, 2, 0, 1, 1
3, 1, 1, 0, 1
0, 2, 1, 1, 0


I know I can delete the not invited or can sort them to the bottom and just use an =sum function and just not use them in the formula, but I don't want to have to delete the people not invited or re-sort the list if I don't have to because they are in a certain order : /.

Hope it makes sense so you can help.