Newbie Struggling

[Dean]

Hi Group,

I am using Excel for the first time and have 2 questions.

1. I have created a spreadsheet that includes some blank
rows. Is there a way to add numbers in a column that
contains some blank cells. In other words, can I add the
numbers in the row and skip the blank cells. These blank
cells seem to be confusing the "sum" function, but I need
them to remain for clarity purposes.

2. Is there a way to create a conditional format that
would accomlish the following: If the letter A appears
in A1, then 50 would print in B1, and if XY apprears in
A2, then 75 would appear in B2

I hope I am explaining my questions.

Thanks for your help !

 

[Frank Kabel]

Hi
for your questions
1. Summing: use
=SUM(A1:A100)
this function will ignore empty cells

2. Not a conditional format but two formulas.
B1:
=IF(A1="A",50,"")

B2:
=IF(A2="XY",75,"")

 

[Dean]

Hi Frank,

Thanks for your excellent answers !

Can I ask one more question. Can I combine the two
formulas in my second question and if so, how.

So, if A1=V then B1 would be 50, but if A1=XY, B1 would
be 50, and if A1 = Q, then B1 would be 99, etc.

I'm sure this example sounds confusing out of context,
but it makes sense on my speadsheet.

Thanks again !

 

[Dave Peterson]

I'm not Frank, and I'm not sure what etc means (how far does it go???).

But I think I'd use another worksheet (say sheet2) and build a table.

In column A, put all your values that A1 could assume.
In column B, put all the corresponding values that B1 could take.

Then in B1, put a formula like:

=if(a1="","",vlookup(a1,sheet2!$a:$b,2,false))

 

[Dean]

There are only 3 possible scenerios. If I wanted to use
Frank's formula, could I just expound on it ?? And if
so, how?

So I am attempting to combine these three (for example):

B1: =IF(A1="A",50,"")
=IF(A1="B",75,"")
=IF(A1="C",100,"")

I'm sure it involves the placement of comma's and
parenthesis, I'm just not sure how and where.

Thanks !

 

[Dean]

There are only 3 possible scenerios. If I wanted to use
Frank's formula, could I just expound on it ?? And if
so, how?

So I am attempting to combine these three (for example):

B1: =IF(A1="A",50,"")
=IF(A1="B",75,"")
=IF(A1="C",100,"")

I'm sure it involves the placement of comma's and
parenthesis, I'm just not sure how and where.

Thanks !

 

[JE McGimpsey]

One way:

If there are truly only three scenarios ("A","B", or "C"):
=IF(A1="A",50,IF(A1="B",75,100))

or

=CHOOSE(CODE(A1)-64,50,75,100)


If you have three valid scenarios, and anything else should return "":

=IF(A1="A",50,IF(A1="B",75,IF(A1="C",100,"")))

 

[Dean]

Thanks, I think that was the answer I needed.

Appreciate the help !

 

[jim_millions]

=CHOOSE(CODE(A1)-64,50,75,100)

Can you explain CHOOSE and CODE?

Where did you get 64 and why -64????




you wrote:

 

[jim_millions]

how would be the table in sheet2 look like?

sheet2 (here is the table in sheet2) correct me if i'm worng

V 50
XY 50
Q 99

why do we need sheet2?
Can we do everything in sheet1?

I am confuse. B1 is in sheet2.
is this formula =if(a1="","",vlookup(a1,sheet2!$a:$b,2,false))
plug in sheet1 or sheet2?
If in sheet1, and so far sheet1 is totally balnk.

see my confusion?



you wrote:
But I think I'd use another worksheet (say sheet2) and build a table.

In column A, put all your values that A1 could assume.
In column B, put all the corresponding values that B1 could take.

Then in B1, put a formula like:

=if(a1="","",vlookup(a1,sheet2!$a:$b,2,false)