Need Help with IF in formula.

[bamaisgreat]

In cell H10 i have the following formula =(E10*F10*G10*0.2836)*(F10=0)+(F10*G10) in cell J10 the user will type in the letters CS or SS. I need the formula in H10 to look in this cell and if there is a CS the multipilier need to be 0.2836 if there is a SS the multipiler needs to be 0.3126.

Thanks as always.

Jamey

 

[SteveO59L]

=if(j10="CS",=((E10*F10*G10*0.2836)*(F10=0)+(F10*G10))*.2836,(=(E10*F10*G10*0.2836)*(F10=0)+(F10*G10))*.3126)

 

[Gandor]

The first part of your equation can be written as =lookup(J10,{"CS","SS"},{".2836",".3126"})*E10*F10*G10
You should have some kind of validation on J10 to prevent unwanted codes from being entered.

Not sure what is happening with the part: (F10=0)+(F10*G10)

 

[bbott]

Another option:

=PRODUCT(LOOKUP(J10,{"CS","SS"},{0.2836,0.3126}),E10:G10)+F10*G10

Also, if you're using F10=0 as a Boolean to zero out your first argument, there's no need. If F10 equals 0, it will already do that in your calculation.

 

[jumbledore]

deleted

 

[bamaisgreat]

ok thanks for all you help. I am however getting a #N/A in all the rows that do not have data in them. Is there away for this to show blank until data is entered.

 

[bbott]

ok thanks for all you help. I am however getting a #n/a in all the rows that do not have data in them. Is there away for this to show blank until data is entered.

=iferror(product(lookup(j10,{"cs","ss"},{0.2836,0.3126}),e10:g10)+f10*g10,"")

 

[vds1]

Hello,

Are you sure your formula is right ?

I have two points to comment,

1) . if F10 equal to 0 then your result in J10 will always be 0
2) . if F10 is not Equal to 0 then result in J10 will always be F10 * G10

Hence your multiplier becomes insignificant.

 

[bamaisgreat]

ok in addition to =iferror(product(lookup(j10,{"cs","ss"},{0.2836,0.3126}),e10:g10)+f10*g10,"") in i10 I have simply =H10*B10 . If there is not data yet entered it is showing #VALUE. Also when I drag this formula down and sum it up in I35 i need it to be for the sum of everything with CS and in I36 the sum of everything with SS.
Thanks

 

[bbott]

You can use the same IFERROR function:

=IFERROR(H10*B10,"")

Take a look at the SUMIF function for your totals.