Excel Formula

J

Jim Austin

Trying to write a formula that would say this:
If there is an "x" in column F2 then add 1 to the number in column I2.
In other words column I2 starts with a "0" and if there is a "x" placed
in F2, then I want I2 to read "1". This is a win loss chart so there
would be "x's" in other columns but I want it to keep adding "1" to I2.


** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins & Business Software Galore!
Free Excel Forum http://www.ozgrid.com/forum ***
 
D

Don Guillett

A bit more clarity before suggesting a worksheet_change event. Are you
saying that if x is put in f2 increase i2 by 1 and then if x is entered
somewhere else______? the add another 1 to i2 ????
 
J

Jim Austin

Yes. Column I2 is dedicated to a team. Each time there is an "x" placed
in a column indicating a win, then I want I2 to increase by 1. Right now
it is at "0". So if this team wins then "x" would be placed and I2 would
read "1". Then the next time they win and another "x" is placed, then I2
would read "2".

** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins & Business Software Galore!
Free Excel Forum http://www.ozgrid.com/forum ***
 
F

Frank Kabel

Hi
where would you place your second 'x'.
Also you may be precise in which column you want to place the 'x'
values. I2 is not a column but a cell
 
F

Frank Kabel

Hi Jim
this could be done using an event procedure (if you always want to put
an 'x' in the same cell). But I'd suggest you change your spreadsheet
design. Reasons:
- what should happen if you accidently place an 'x' in this cell.
- you don't have an 'audit' trail

Why don't you store all results in a table for the team?
 
D

Don Guillett

Gee. This mind reading is tough. Can't you tell us what you are trying to do
with an example?
 
J

Jim Austin

Don

Right now I2 reads "0". Each time I place a "x" in cell F3, F15, F32,
F44, F51, F65, F75 I want I2 to increase by "1". This will keep track of
wins in their 7 games. Don't know how else to explain it.

** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins & Business Software Galore!
Free Excel Forum http://www.ozgrid.com/forum ***
 
F

Frank Kabel

Hi
one question :)
Why are your cells with entries so scattered accros column F (there's
no logic behind it). If they would have the same offset from each other
a formula could simply be derived.
another question: What is in the other cells in column F?
 
R

RagDyer

Not knowing your setup, I have no idea what's in the *rest* of your column F
.... BUT ... try this ... it *may* just work, depending on what's in the
balance of the column:

=COUNTIF(F3:F75,"x")

If it doesn't, post back.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


Don

Right now I2 reads "0". Each time I place a "x" in cell F3, F15, F32,
F44, F51, F65, F75 I want I2 to increase by "1". This will keep track of
wins in their 7 games. Don't know how else to explain it.

** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins & Business Software Galore!
Free Excel Forum http://www.ozgrid.com/forum ***
 
F

Frank Kabel

Hi Jim
problem is, we are all a little bit guessing how your spreadsheet looks
like (you give only small pieces of information). Try the following:
post some example rows of your spreadsheet (plain text - no attachment
please) so that we can understand your setup
 
R

RagDyer

Don't know if this is good enough for your purposes, but until someone comes
up with something more sophisticated, this is a straight brute force
approach with *NO* error checking, and one *BIG* stipulation:

<<<The cells in column F *must* be empty!>>>

If a team wins, *ANYTHING* you place in those particular cells will be
counted as a win.
So you could enter *any* letter to designate a win.

=COUNTA(F3,F15,F32,F44,F51,F65,F75)
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================



RD

It works. Problem is that this formula calls for the count in cells F3
through F75. Now if I could get it to work for just cells F3, F15, F32,
F44, F51, F65, F75.

** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins & Business Software Galore!
Free Excel Forum http://www.ozgrid.com/forum ***
 
J

Jim Austin

For everyone that responded to my initial help request, I have solved
the problem all by myself. It may not be the right way but it has been
tested and works 100%. I used the =IF for the first cell and then +IF
for the remainder of the cells and it works. This was the first formula
for cell I2:
=IF(F3="x",1)+IF(F15="x",1)+IF(F32="x",1)+IF(F44="x",1)+IF(F51="x",1)+IF
(F65="x",1)+IF(F75="x",1). I then copied and modified for the remainder
of the teams. Thanks RD for your suggestion.

** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins & Business Software Galore!
Free Excel Forum http://www.ozgrid.com/forum ***
 
D

Don Guillett

<<<The cells in column F *must* be empty!>>>
=COUNTA((F2,F6,H2,H5,K8))
worked just fine no matter what was there. However, will count other than x.
 

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

Similar Threads

link to other workbooks 2
Excel formula doesn't work when put in from VBA, but works when written in Excel 2
Sum cells flagged by other cells 2
Two Excel forms- swapping data. 1
User Defined Function 2
Find formula not calculating? 3
Change sheet tab color in formula 2
qty to change all to 0 2

Top