creating rule to adjust fields..

**Symbiot** · 03-01-2009, 12:48 PM

ok.. first of all..

I'm a complete Excel newb..
secondly.. I have no idea how to compose a proper topic title for my problem.. so I hope it makes sense..

I want to do what I've highlighted in the image attached...

it might be super simple.. but I have no idea how.

well, there's alot more I want to add, but this is a start, and will let me get on with it.

hope you can help!

**JBeaucaire** · 03-01-2009, 12:54 PM

Don't really need the image, you could post that verbiage in yellow into your question and it's pretty clear.

=IF(C5="x",E2/1000*B9*C18,0)

You didn't indicate what to do if C5 doesn't have an "x" in it, so I just inserted a zero.

**Symbiot** · 03-01-2009, 01:00 PM

Thanks!!!

well.. ok. .no I guess I didn't.

what I need is for the result to take into account is:

IF x in C5 then E2 / 1000*B9*C18, if x in D5 then E2 / 1000*B9*D18, if x in B5 then E2 / 1000*B9*B18, in no x then error because user MUST select.

so the 1 result must take all 3 into account...

make sense?

**shg** · 03-01-2009, 01:39 PM

=IF(COUNTIF(C5:E5, "x") > 0, E2 * B9 * C18 / 1000, "Error")

**Symbiot** · 03-01-2009, 01:45 PM

Hi shg.

thanks.

on adding it to the sheet I get an error, also the C5:e5 should be B5:d5

also the formula you suggest doesn't change the last part:

IF x in C5 then E2 / 1000*B9*C18, if x in D5 then E2 / 1000*B9*D18, if x in B5 then E2 / 1000*B9*B18, in no x then error because user MUST select.

I'm sorry I cannot suggest something myself.. but as I wrote.. I have no idea..
I can see the logic in what you're suggesting, but not what's missing.

**shg** · 03-01-2009, 02:41 PM

Maybe =IF(COUNTIF(B5:D5, "x") <> 1, "Error", SUMPRODUCT( (B5:D5="x") * B18:D18) * E2 * B9 / 1000 )

It's way easier to help if you post workbooks rather than pictures.