asked on

round down to max number

Hi,
I'd like to round down a number to a max of 99
I'm using a very long formula containing multiplication of around 20 variables
so the below formula example would be too long
=IF(a1*b1*c1*d1*e1 etc>99,99,a1*b1*c1*d1*e1 etc
There must be a simpler way.
Help appreciated
Thanks
Ian

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Shums Faruk

Please try this instead of multiplying each cell, you can select the range with Product Function.

=ROUND(PRODUCT(A1:E1),2)

Open in new window

Shaun Vermaak

It is not the MAX down that is making your formula long/complex, it is the formula itself.
Can you use something like

=PRODUCT(A1:E1)

Open in new window

Shaun Vermaak

Something like...

=IF(PRODUCT(A1:E1)>99,99,PRODUCT(A1:E1))

Open in new window

Shums Faruk

Try below, if multiplication is greater than 99.99, then it must show 99.99, else the normal multiplication.

=IF(PRODUCT(A1:E1)>99.99,ROUND(MIN(99.99,PRODUCT(A1:E1)),2),ROUND(PRODUCT(A1:E1),2))

Open in new window

Ian Bell

ASKER

Sorry guys I should have informed you that the columns are not consecutive, hence a range would not work
here is an example, also several columns are multiplied by themselves eg a3*a3*a3 etc

=EJ18*EJ18*EJ18*EJ18*EJ18*EJ18*EJ18*EJ18*EJ18*EJ18*EJ18*EJ18*EA18*EA18*CB18*CB18*CE18*CT18*CZ18*CZ18*CZ18*CZ18*CZ18*CZ18*CZ18*CZ18*CZ18*CZ18*DC18*DI18*DI18*DI18*DI18*DI18*DI18*DI18*DI18*DI18*DI18

Shums Faruk

Why the same cell multiplied few times? Please give us some more info or attach a sample workbook.

SOLUTION