I would like to simplify (reduce) the number of IF statements in this
formula: -
=IF(H901,G90*M90*P90,IF(I901,G90*M90*P90,IF(J90 1,G90*M90*P90,IF(K901,G90*M90*P90,IF(L901,G90*M9 0*P90,(G90*7850*(N90/1000*O90/1000*Q90)))))))

Without a Macro, as there is already macros operating elsewhere in the
spreadsheet. I was thinking of something like:

=IF(H90:L901,G90*M90*P90,,(G90*7850*(N90/1000*O90/1000*Q90)), but this
returns a #VALUE error.

Can anyone help?

Please post future worksheet formula questions to the worksheet.functions
group.

To answer your question, here are two options:

If you need to check each cell value individually, use an OR statement:

IF(or(H901, I901, J901, K901, L901), G90*M90*P90,
(G90*7850*(N90/1000*O90/1000*Q90)))

I don't know anything about your data, but if those 5 cells will have a
value of 1 by default (instead of potentially zero) you could also just sum
them and look for a value greater than 5, which by default means that one of
them has to be 1:

If (sum(H90:L90)5, ), G90*M90*P90, (G90*7850*(N90/1000*O90/1000*Q90)))

HTH,
Keith

"Ray" wrote:

I would like to simplify (reduce) the number of IF statements in this
formula: -
=IF(H901,G90*M90*P90,IF(I901,G90*M90*P90,IF(J90 1,G90*M90*P90,IF(K901,G90*M90*P90,IF(L901,G90*M9 0*P90,(G90*7850*(N90/1000*O90/1000*Q90)))))))

Without a Macro, as there is already macros operating elsewhere in the
spreadsheet. I was thinking of something like:

=IF(H90:L901,G90*M90*P90,,(G90*7850*(N90/1000*O90/1000*Q90)), but this
returns a #VALUE error.

Can anyone help?

It's late in my day, so my analytical cells may be sleeping, but it looks to
me like the "OR" in Keith's suggested formula should be an "AND". But his
other idea is definitely simpler if the assumptions are correct.

"ker_01" wrote:

Please post future worksheet formula questions to the worksheet.functions
group.

To answer your question, here are two options:

If you need to check each cell value individually, use an OR statement:

IF(or(H901, I901, J901, K901, L901), G90*M90*P90,
(G90*7850*(N90/1000*O90/1000*Q90)))

I don't know anything about your data, but if those 5 cells will have a
value of 1 by default (instead of potentially zero) you could also just sum
them and look for a value greater than 5, which by default means that one of
them has to be 1:

If (sum(H90:L90)5, ), G90*M90*P90, (G90*7850*(N90/1000*O90/1000*Q90)))

HTH,
Keith

"Ray" wrote:

I would like to simplify (reduce) the number of IF statements in this
formula: -
=IF(H901,G90*M90*P90,IF(I901,G90*M90*P90,IF(J90 1,G90*M90*P90,IF(K901,G90*M90*P90,IF(L901,G90*M9 0*P90,(G90*7850*(N90/1000*O90/1000*Q90)))))))

Without a Macro, as there is already macros operating elsewhere in the
spreadsheet. I was thinking of something like:

=IF(H90:L901,G90*M90*P90,,(G90*7850*(N90/1000*O90/1000*Q90)), but this
returns a #VALUE error.

Can anyone help?

Another way using COUNTIF()

=IF(COUNTIF(H90:L90,"1"),true statement,falsestatement)

=IF(COUNTIF(H90:L90,"1"),G90*M90*P90,G90*7850*(N9 0/1000*O90/1000*Q90)

If this post helps click Yes
---------------
Jacob Skaria


"Ray" wrote:

I would like to simplify (reduce) the number of IF statements in this
formula: -
=IF(H901,G90*M90*P90,IF(I901,G90*M90*P90,IF(J90 1,G90*M90*P90,IF(K901,G90*M90*P90,IF(L901,G90*M9 0*P90,(G90*7850*(N90/1000*O90/1000*Q90)))))))

Without a Macro, as there is already macros operating elsewhere in the
spreadsheet. I was thinking of something like:

=IF(H90:L901,G90*M90*P90,,(G90*7850*(N90/1000*O90/1000*Q90)), but this
returns a #VALUE error.

Can anyone help?