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

A

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
 
J

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
 
F

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
 
A

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.
 
J

Jacob Skaria

Try this

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


If this post helps click Yes
 
A

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
 
A

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
 
A

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]
 
A

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


Andy_N1708 via OfficeKB.com said:
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
 
A

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]
 
A

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

Andy_N1708 via OfficeKB.com said:
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
 
A

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

Andy_N1708 via OfficeKB.com said:
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
 
A

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]
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top