IF THEN statement IN cell

concatenated ifs

say your inputed value goes in b8, your "s/p/f" in c8, put the following in d8
=if(c8="s",0,"")&if(c8="p",b8*.5,"")&if(c8="f",c8,"")
I'm no excel wizard, I just sweep up around here! Hope that helps anyway.

> I want to write a formula in the same cell a
> number will be entered in.

You can enter into a cell either a value or a
formula, but not both.

Re: concatenated ifs.. NEED MORE HELP!!!

that's pretty much getting the job done, HOWEVER:
1) for some reason, I can't format the cell (number).
it doesn't "take" any formatting I attempt to apply.

2) isn't there a way to have this formula in the ACTUAL
cell that contains the value? (I'd rather not have
to add another column and change the other formulas
to refer to THAT cell.)

thanks.

Re: concatenated ifs.. NEED MORE HELP!!!

> isn't there a way to have this formula in the
> ACTUAL cell that contains the value?

See 29154.html

Re: concatenated ifs.. CAN'T FORMAT

ok. but what about not being able to format. > isn't there a way to have this formula in the

Re: concatenated ifs.. CAN'T FORMAT

Is your chosen format compatible with the data
type returned by your formula? ok. but what about not being able to format. : > isn't there a way to have this formula in the

=VALUE(IF(C8="s",0,"")&IF(C8="p",B8*0.5,"")&IF(C8="f",C8,""))

Try the following, I was able to format with no problems
=VALUE(IF(C8="s",0,"")&IF(C8="p",B8*0.5,"")&IF(C8="f",C8,""))
as for changing values within cells, that's a capability of virtual basic- the people on the board with expertise in that area might be able to help.

On your own you might be able to record a macro that made the new formula column, copied the new results, pasted them as values back into the original column, and then deleted the formula column. Just an idea.

Terribly sorry I couldn't get you any farther than this. ok. but what about not being able to format. : > isn't there a way to have this formula in the

Re: =VALUE(IF(C8=

I don't know where this post went, I'll try again.
Ok, everything is just about set, except I want to
copy that formula down the column, however when
there is no referring info in C8 *yet*, I want the
formula column to remain BLANK, not say "#VALUE!".
I assume I have to add another IF statement to
current one, but I can't seem to figure it out.

thank you. Try the following, I was able to format with no problems

=IF(OR(C8="f",C8="s",C8="p"),VALUE(IF(C8="s",0,"")&IF(C8="p",B8*0.5,"")&IF(C8="f",B8,"")),"")

Try
=IF(OR(C8="f",C8="s",C8="p"),VALUE(IF(C8="s",0,"")&IF(C8="p",B8*0.5,"")&IF(C8="f",B8,"")),"")

when you want it to return a blank if there is not an "f" or an "s" or a "p" in col(c)

try
=IF(ISERROR(VALUE(IF(C8="s",0,"")&IF(C8="p",B8*0.5,"")&IF(C8="f",B8,""))),"",VALUE(IF(C8="s",0,"")&IF(C8="p",B8*0.5,"")&IF(C8="f",B8,"")))

to blank all times the formula has an error (sort of the classic "IF statement to blank an error" layout)

or try
=VALUE(IF(C8="s",0,"")&IF(C8="p",B8*0.5,"")&IF(C8="f",B8,"")&IF(ISERROR(C8),"",""))

to do the same thing with less formula (not as applicable to all situations though) Try the following, I was able to format with no problems

oops, never mind the third formula in my last post, dumb idea (NT)

Re: =VALUE(IF(C8=

Try this:

=if(type(VALUE(IF(C8="s",0,"")&IF(C8="p",B8*0.5,"")&IF(C8="f",C8,"")))=16,"",VALUE(IF(C8="s",0,"")&IF(C8="p",B8*0.5,"")&IF(C8="f",C8,"")))

Type function converts error value to numeric 16, lets you use if statement to replace error value with blank text. I've used this a lot to eliminate the impact of error values when reports are populated with lookups and there may or may not be matching data in the lookup table for each of the items (replace =16,"" with =16,0). Good luck!