if then help please

[Steven]

gremalcon (is the name) and it is at hotmail, just another dot com

I need your email address to mail direct

--
Regards

Roger Govier

I tried it in c and could not get it to work. Can you send me back the one
you did that worked?

Hi Steven

I apologise. I should have said enter the formula in C2 and copy down.
Obviously entering a formula in B2 which said use B2 as the reference
would give a circular reference error.
It does work.
I tried it on your spreadsheet before posting the solution,
unfortunately I made the typo of saying B2 rather than C2.

--
Regards

Roger Govier


I cannot have any equations in the B column. I need to be able to enter
what level/letter the character is in that column. I also need to be
able to enter a P in the D column if dues had been paid. C is the only
column that can have any equations in it.

Steven

Mark cells I3:I14
Cut
Move cursor to cell F3, right click>Insert Cut Cells

In B2 enter
=VLOOKUP(B2,$F$3:$I$14,4,0)
Mark cells C2:C14
Format>Conditional Formatting>use drop down to select Formula Is>
=$D2="" >Format>Select Red font>OK

--
Regards

Roger Govier


As you all can tell, I stink at explaining what I want. So I have
attached an example with a bit more explanation. There are no macros
or anthing else and I am virus free (that is one thing I am good at,
security and safety) so it is safe to view it. I hope that one of you
can give me a formula for the dues column that will accomplish this.
Let me say that I really appreciate the help that has been given so
far. You guys are great.

TIA

Steven

Hi Steven

Slight problem with terminology
Single digit would imply 0 to 9, not 0 to 60 as once we get past 9
we
would have 2 digits in the cell.
Single digit A to L should be single character, A to L as these are
alpha characters not digits.

If you decide to go with the digit entry, then the first of my 2
solutions will work fine whether you type 6 or 23 or any other
number
into the cell.

If you are going to go the text route, then either single characters
A
through L or "1 -5", "6 - 10" will work using the second method.
just
make the entries in the first column of the table Dues, as A, B, C
etc.

--
Regards

Roger Govier


the entry will be was going to be a single digit, 1 - 60 but I
think
in order to simplify it, it can be a single digit, A - L
representing
the 12 level groups. Thanks for asking that question, it clarified
that for me. I don't think logically enough, that's for sure.

Hi Steven

It is still unclear (to me at least), whether the entry in column
B
will be either 1 or 2 or 3 etc. up to 60 or whether it will be
"1-5"
or "6-10" etc. up to "56-60".

If the former, then on another part of the sheet (or even a
different
sheet) set up a table as follows

0 0.20
6 0.40
11 0.60
.
.
60 2.40

Mark the range of this 2 column table, and in the Name box (the
small
square to the left of column A and above row 1) type Dues and
press
Enter

Then using Erik's idea enter in C1
=IF(B1="","",IF(D1="P",VLOOKUP(B1,Dues,2),-VLOOKUP(B1,Dues,2)))

If, on the other hand, you are using "1-5" etc. in B1, then create
a
table as follows
(Note: all the entries of 1-5 etc will either have had to have
the
cells pre-formatted as Text, or you will need to precede the
entries
with a single quote '1-5 to force them to be text vales)

1-5 0.20
6-10 0.40
11-15 0.60
etc.
Again name this table as Dues as outlined before.

Now, use the formula in C1
=IF(B1="","",IF(D1="P",VLOOKUP(B1,Dues,2,0),-VLOOKUP(B1,Dues,2,0)))

Your entries in column B will also have to be Text entries.
Format>Cells>Text
--
Regards

Roger Govier


I really have not explained what I want clearly. Please forgive
me. I
will try to be more exact. I have three columns. The first (B) is
the
level a person is at. This can be 1-60, in sets of 5, so 1-5, 6-10
and so on. The second column (C) is the guild dues they owe, which
is
based on their character level range. Everyone who is level 1-5
owes
the same guild dues, while everyone who is 6-10 owes a higher
amount
and so on. So what I need is to be able to put their level in (B)
and
have it bring up one of 12 different dues amounts, based on the
level
entered in (B). So if I enter a number between 1 and 5, column (C)
would automatically show the one value out of the 12 that
corresponds
to that level. Column (D) needs to be either blank or have a P in
it.
If blank, then the figure in (C) would be red or negative and if
there is a P, then it would be black/positive. This is for a game,
where we all pay game money to the guild, to help it grow. I am in
charge of keeping track of who pays and who does not, so I need
something to make it easier and quicker.

So something like [if b = 1-5 then 20c if 6-10 then 40c, if 11-15
then 80c on to 56-60 and if (D) is blank then (C) is negative and
if
(D) is P then (C) is positive/paid.

Steven,

If you fill H1 to H13 with 1,6,11,16 and so on to 61, and
if you fill I1 to I13 with 20,40,60,80 and so on to 260, and
enter in C1 formula: =LOOKUP(B1,$H$1:$I$13) and copy this
formula
down,
then the result is 20 in C1 for values 1,2,3,4,5 in B1

=IF(D1="P";LOOKUP(B1,$H$1:$I$13);-LOOKUP(B1,$H$1:$I$13))
will make this value positive if D1 is P, or negative if it is
not.

HTH
Erik

"Steven" <[email protected]> schreef in bericht
Sorry, but that makes no sense to me, I don't know enough.

Expand on this idea
=LOOKUP(E1,{0,1,6,11,16;0,1,2,3,4})

--
Don Guillett
SalesAid Software
(e-mail address removed)
Thank you so far. I still have questions though. Will that
work
for several sets of ranges? I need it to be 1-5, 6-10, 11-15,
on
up to 60. Also, I think D would be simplest if it was either
P
or blank. I also need to set C to be whatever value
corresponds
to the specific range, like 1-5 makes C 20, while 6-10 makes
C
40 and so on. I am sorry I did not think this through
properly
before making my original post.

message
Hi Steven

Maybe
=IF(AND(B1>0,B1<=5),999*IF(D1="",-1,1),"")

Change the 999 to any number you want to use.

--
Regards

Roger Govier


I have three columns, B, C and D. In B I would have ranges
such
as 1-5 6-10 and so on. In D I would have yes or now, paid or
not paid. I would kind of like it to be checked or not
checked
if possible. C must be something like "if B is 1,2,3,4 or 5,
then xxx and if D is yes or checked it is a positive and if
it
is empty then it is a negative." I kind of have an idea how
to
do this, but nothing I try works. How do I accomplish this?

TIA

Steven

"A democracy is nothing more than mob rule,
where
fifty-one percent of the people may take away the rights of
the other forty-nine." - Thomas Jefferson

 

[Erik Veldkamp]

Steven,

I don't see an attachment and I doubt an attachment is allowed is the group.

To write: "if B2 equals I3 then let C2 be equal to H3"

the formula in cell C2 can be: =IF(B2=I3,H3,0)

To write: "if B2 equals I3 then let C2 be equal to H3, else if B2 equals I4
let C2 be equal to H4",

the formula in cell C2 can be: =IF(B2=I3,H3,IF(B2=I4,H4),0))

To write: "if B2 equals I3 then let C2 be equal to H3, else if B2 equals I4
let C2 be equal to H4, else if B2 equals I5 let C2 be equal to H5",

the formula in cell C2 can be: =IF(B2=I3,H3,IF(B2=I4,H4),IF(B2=I5,H5),0)))

and so on

There used to be a limit to the amount of IFs you can put in one formula, so
you should consider that the formula in cell C2 can be:

=VLOOKUP(B2,I3:H14,2) but then first switch I3:I14 with H3:H14.

Erik

 

[Roger Govier]

Hi Steven

Copy sent.

Steven had decided to go with categories A to L rather than using
numeric's of 1 to 60 in column B, or using text categories of "1 - 5"
etc.
He had set up a lookup table, in cells F3:I14 but had put the A to L to
the far right of the table in column I, hence no use for Vlookup.
It could of course have been solved with Index/Match, but I thought it
was easier to cut column I and insert at column F and stick with Vlookup
=IF(B2="","",VLOOKUP(B2,$F$3:$I$14,4,0))

A further complication which had thrown those of us who had replied, was
his "values" of 20c 40c etc. were not numeric 20 cents or 40 cents, but
part of some mixture of coins where 1s 60c means 1 silver and 60 copper
coins.
The request to have unpaid dues show up in red was therefore achieved by
Conditional formatting of the cells with the Vlookup formula with
Formula Is =$D2="" and Red font chosen.

--
Regards

Roger Govier

gremalcon (is the name) and it is at hotmail, just another dot com

I need your email address to mail direct

--
Regards

Roger Govier

I tried it in c and could not get it to work. Can you send me back
the one you did that worked?

Hi Steven

I apologise. I should have said enter the formula in C2 and copy
down. Obviously entering a formula in B2 which said use B2 as the
reference would give a circular reference error.
It does work.
I tried it on your spreadsheet before posting the solution,
unfortunately I made the typo of saying B2 rather than C2.

--
Regards

Roger Govier


I cannot have any equations in the B column. I need to be able to
enter what level/letter the character is in that column. I also
need to be able to enter a P in the D column if dues had been paid.
C is the only column that can have any equations in it.

Steven

Mark cells I3:I14
Cut
Move cursor to cell F3, right click>Insert Cut Cells

In B2 enter
=VLOOKUP(B2,$F$3:$I$14,4,0)
Mark cells C2:C14
Format>Conditional Formatting>use drop down to select Formula Is>
=$D2="" >Format>Select Red font>OK

--
Regards

Roger Govier


As you all can tell, I stink at explaining what I want. So I
have attached an example with a bit more explanation. There are
no macros or anthing else and I am virus free (that is one thing
I am good at, security and safety) so it is safe to view it. I
hope that one of you can give me a formula for the dues column
that will accomplish this. Let me say that I really appreciate
the help that has been given so far. You guys are great.

TIA

Steven

Hi Steven

Slight problem with terminology
Single digit would imply 0 to 9, not 0 to 60 as once we get
past 9 we
would have 2 digits in the cell.
Single digit A to L should be single character, A to L as these
are
alpha characters not digits.

If you decide to go with the digit entry, then the first of my
2
solutions will work fine whether you type 6 or 23 or any other
number
into the cell.

If you are going to go the text route, then either single
characters A
through L or "1 -5", "6 - 10" will work using the second
method. just
make the entries in the first column of the table Dues, as A,
B, C etc.

--
Regards

Roger Govier


the entry will be was going to be a single digit, 1 - 60 but I
think
in order to simplify it, it can be a single digit, A - L
representing
the 12 level groups. Thanks for asking that question, it
clarified
that for me. I don't think logically enough, that's for sure.

message
Hi Steven

It is still unclear (to me at least), whether the entry in
column B
will be either 1 or 2 or 3 etc. up to 60 or whether it will
be "1-5"
or "6-10" etc. up to "56-60".

If the former, then on another part of the sheet (or even a
different
sheet) set up a table as follows

0 0.20
6 0.40
11 0.60
.
.
60 2.40

Mark the range of this 2 column table, and in the Name box
(the small
square to the left of column A and above row 1) type Dues and
press
Enter

Then using Erik's idea enter in C1
=IF(B1="","",IF(D1="P",VLOOKUP(B1,Dues,2),-VLOOKUP(B1,Dues,2)))

If, on the other hand, you are using "1-5" etc. in B1, then
create a
table as follows
(Note: all the entries of 1-5 etc will either have had to
have the
cells pre-formatted as Text, or you will need to precede the
entries
with a single quote '1-5 to force them to be text vales)

1-5 0.20
6-10 0.40
11-15 0.60
etc.
Again name this table as Dues as outlined before.

Now, use the formula in C1
=IF(B1="","",IF(D1="P",VLOOKUP(B1,Dues,2,0),-VLOOKUP(B1,Dues,2,0)))

Your entries in column B will also have to be Text entries.
Format>Cells>Text
--
Regards

Roger Govier


I really have not explained what I want clearly. Please
forgive me. I
will try to be more exact. I have three columns. The first
(B) is the
level a person is at. This can be 1-60, in sets of 5, so 1-5,
6-10
and so on. The second column (C) is the guild dues they owe,
which is
based on their character level range. Everyone who is level
1-5 owes
the same guild dues, while everyone who is 6-10 owes a higher
amount
and so on. So what I need is to be able to put their level in
(B) and
have it bring up one of 12 different dues amounts, based on
the level
entered in (B). So if I enter a number between 1 and 5,
column (C)
would automatically show the one value out of the 12 that
corresponds
to that level. Column (D) needs to be either blank or have a
P in it.
If blank, then the figure in (C) would be red or negative and
if
there is a P, then it would be black/positive. This is for a
game,
where we all pay game money to the guild, to help it grow. I
am in
charge of keeping track of who pays and who does not, so I
need
something to make it easier and quicker.

So something like [if b = 1-5 then 20c if 6-10 then 40c, if
11-15
then 80c on to 56-60 and if (D) is blank then (C) is
negative and if
(D) is P then (C) is positive/paid.

Steven,

If you fill H1 to H13 with 1,6,11,16 and so on to 61, and
if you fill I1 to I13 with 20,40,60,80 and so on to 260,
and
enter in C1 formula: =LOOKUP(B1,$H$1:$I$13) and copy this
formula
down,
then the result is 20 in C1 for values 1,2,3,4,5 in B1

=IF(D1="P";LOOKUP(B1,$H$1:$I$13);-LOOKUP(B1,$H$1:$I$13))
will make this value positive if D1 is P, or negative if it
is not.

HTH
Erik

"Steven" <[email protected]> schreef in bericht
Sorry, but that makes no sense to me, I don't know enough.

Expand on this idea
=LOOKUP(E1,{0,1,6,11,16;0,1,2,3,4})

--
Don Guillett
SalesAid Software
(e-mail address removed)
Thank you so far. I still have questions though. Will
that work
for several sets of ranges? I need it to be 1-5, 6-10,
11-15, on
up to 60. Also, I think D would be simplest if it was
either P
or blank. I also need to set C to be whatever value
corresponds
to the specific range, like 1-5 makes C 20, while 6-10
makes C
40 and so on. I am sorry I did not think this through
properly
before making my original post.

message
Hi Steven

Maybe
=IF(AND(B1>0,B1<=5),999*IF(D1="",-1,1),"")

Change the 999 to any number you want to use.

--
Regards

Roger Govier


I have three columns, B, C and D. In B I would have
ranges such
as 1-5 6-10 and so on. In D I would have yes or now,
paid or
not paid. I would kind of like it to be checked or not
checked
if possible. C must be something like "if B is 1,2,3,4
or 5,
then xxx and if D is yes or checked it is a positive
and if it
is empty then it is a negative." I kind of have an idea
how to
do this, but nothing I try works. How do I accomplish
this?

TIA

Steven

"A democracy is nothing more than mob rule,
where
fifty-one percent of the people may take away the
rights of
the other forty-nine." - Thomas Jefferson

 

[Steven]

Ok, everything is working and set up now. Thanks to everyone who helped,
especially Roger Govier who gave me the solutions I needed.