Trying to calculate results in one cell, based on data of another

[ADC76]

Hello,
Here's my problem: If A1 is less than or equal to 49.95, I need B1 to show
a result of 12.48. If A1 is greater than 49.95, I need B1 to multiply the A1
number by 50%.
Here's the formula I've been trying to use, but it isn't working the way I
need it to:
=IF(AND(A1<=49.95,B1=12.48,IF(A1>49.95,B1*0.5,0)))
I really don't know what I'm doing wrong. Any suggestions would be great.
Thanks,
ADC76

 

[Jacob Skaria]

You mean A1*.5 right?. Formula in B1

=IF(A1<=49.95,12.48,A1*0.5)

If this post helps click Yes

 

[FSt1]

hi
first, your formula is confusing, on really reflecting you discriptive need.
try this. in B1 enter this formula
=IF(A1<=49.95,12.48,A1*0.5)

if i got confused, post back.
and remember....formulas return values to the cell in which they reside.
they cannot put values in other cells or perform any other action.

Regards
FSt1

 

[ADC76]

That seems to work great. But, now I realize that I want the B1 cell blank
unless there is data entered in the A1 cell. 12.48 is in the B1 cell all the
time now.

 

[Jacob Skaria]

Try this

=IF(A1<=0,"",IF(A1<=49.95,12.48,A1*0.5))


If this post helps click Yes

 

[ADC76]

Hey FSt1,
You and Jacob Skaria were both right, based on the information I gave you.
however the problem is now that 12.48 shows up even when A1 is blank. How do
I get B1 to stay blank unless data is entered in? Thanks for your help.
ADC76

 

[ADC76]

OK. That formula is working great! Unfortunately it seems to be throwing of
the calcuations of another cell all together. I'm now getting a #VALUE error
message in my totals cell on on that row. That cell's formula is
H1+B1+C1+D1+E1+F1
The results show in I1. Only when all cells are blank do I get the #VALUE
error. If the data is entered in any cell of Row 1, the formulas work fine.
However, I have a cell at the bottom of column I that is to total all data in
that column, and that formula is not working at all. =SUM(I1:I30)
Thanks for your help.
ADC76

 

[Andy_N1708 via OfficeKB.com]

Use this code

=IF(ISBLANK(A1),"",IF(A1<=49.95,12.48,A1*0.5))

Hey FSt1,
You and Jacob Skaria were both right, based on the information I gave you.
however the problem is now that 12.48 shows up even when A1 is blank. How do
I get B1 to stay blank unless data is entered in? Thanks for your help.
ADC76

hi
first, your formula is confusing, on really reflecting you discriptive need.

[quoted text clipped - 18 lines]

 

[ADC76]

Hey Andy,
That formula works too. However, I seemed to have created another problem
by entering this formula. Here is the post that I replied to Jacob, but have
not heard back from him yet. Maybe you can help.

"Unfortunately it seems to be throwing of the calcuations of another cell
all together. I'm now getting a #VALUE error message in my totals cell on on
that row. That cell's formula is:
H1+B1+C1+D1+E1+F1
The results show in I1. Only when all cells are blank do I get the #VALUE
error. If data is entered in any cell of Row 1, the formulas work fine.
However, I have a cell at the bottom of column I that is to total all data in
that column, and that formula is not working at all. =SUM(I1:I30)
Thanks for your help."
ADC76

Use this code

=IF(ISBLANK(A1),"",IF(A1<=49.95,12.48,A1*0.5))

Hey FSt1,
You and Jacob Skaria were both right, based on the information I gave you.
however the problem is now that 12.48 shows up even when A1 is blank. How do
I get B1 to stay blank unless data is entered in? Thanks for your help.
ADC76

hi
first, your formula is confusing, on really reflecting you discriptive need.

[quoted text clipped - 18 lines]

Thanks,
ADC76

 

[Andy_N1708 via OfficeKB.com]

Change formula to
=IF(ISBLANK(A1),0,IF(A1<=20,12,A1*0.5))

Hey Andy,
That formula works too. However, I seemed to have created another problem
by entering this formula. Here is the post that I replied to Jacob, but have
not heard back from him yet. Maybe you can help.

"Unfortunately it seems to be throwing of the calcuations of another cell
all together. I'm now getting a #VALUE error message in my totals cell on on
that row. That cell's formula is:
H1+B1+C1+D1+E1+F1
The results show in I1. Only when all cells are blank do I get the #VALUE
error. If data is entered in any cell of Row 1, the formulas work fine.
However, I have a cell at the bottom of column I that is to total all data in
that column, and that formula is not working at all. =SUM(I1:I30)
Thanks for your help."
ADC76

Use this code

[quoted text clipped - 11 lines]

 

[ADC76]

The first formula you suggested did the trick. Still having the problem of
the #VALUE error down my entire -I- column and my totals for my -I- column
will not calculate in assigned cell. =SUM(I1:I30)

Question: The original formula that started all this is:
=IF(ISBLANK(A1),"",IF(A1<=49.95,12.48,A1*0.5))
The result is to show in B1.
The cell that totals row 1 has the formula: =SUM(H1+B1+C1+D1+E1+F1)
The cell that totals Column I has the formula: =SUM(I1:I30)
The result is to show in I31
Is there anything that seems odd about this or would cause I31 to not show
the results?
ADC76

Change formula to
=IF(ISBLANK(A1),0,IF(A1<=20,12,A1*0.5))

Hey Andy,
That formula works too. However, I seemed to have created another problem
by entering this formula. Here is the post that I replied to Jacob, but have
not heard back from him yet. Maybe you can help.

"Unfortunately it seems to be throwing of the calcuations of another cell
all together. I'm now getting a #VALUE error message in my totals cell on on
that row. That cell's formula is:
H1+B1+C1+D1+E1+F1
The results show in I1. Only when all cells are blank do I get the #VALUE
error. If data is entered in any cell of Row 1, the formulas work fine.
However, I have a cell at the bottom of column I that is to total all data in
that column, and that formula is not working at all. =SUM(I1:I30)
Thanks for your help."
ADC76

Use this code

[quoted text clipped - 11 lines]

Thanks,
ADC76

 

[ADC76]

Hey Andy,
Just wanted to let you know that I tried plugging in your formula one more
time and tweeked it a little with the right numbers and it worked! The
#VALUE error went away and I duplicated the formula down the column and my
totals cell in I31 working properly.
Thanks for the help!
ADC76

Change formula to
=IF(ISBLANK(A1),0,IF(A1<=20,12,A1*0.5))

Hey Andy,
That formula works too. However, I seemed to have created another problem
by entering this formula. Here is the post that I replied to Jacob, but have
not heard back from him yet. Maybe you can help.

"Unfortunately it seems to be throwing of the calcuations of another cell
all together. I'm now getting a #VALUE error message in my totals cell on on
that row. That cell's formula is:
H1+B1+C1+D1+E1+F1
The results show in I1. Only when all cells are blank do I get the #VALUE
error. If data is entered in any cell of Row 1, the formulas work fine.
However, I have a cell at the bottom of column I that is to total all data in
that column, and that formula is not working at all. =SUM(I1:I30)
Thanks for your help."
ADC76

Use this code

[quoted text clipped - 11 lines]

Thanks,
ADC76

 

[Andy_N1708 via OfficeKB.com]

You're welcome!

Hey Andy,
Just wanted to let you know that I tried plugging in your formula one more
time and tweeked it a little with the right numbers and it worked! The
#VALUE error went away and I duplicated the formula down the column and my
totals cell in I31 working properly.
Thanks for the help!
ADC76

Change formula to
=IF(ISBLANK(A1),0,IF(A1<=20,12,A1*0.5))

[quoted text clipped - 20 lines]