Formual to have Max $ amount

[Carrie]

Good Afternoon,

Currently I have this formula:

=SUM(B6:G6)*(0.173)

But I don't want the caluculation to exceed $711.03

Help

 

[cm]

try this:

=if((SUM(B6:G6)*(0.173))>=711.03,711.03,(SUM(B6:G6)*(0.173)))

 

[Brown Recluse]

How 'bout this argument that puts the text "OVER" if it is over 711.03:
=IF(SUM(B6:G6)*(0.173)>711.03,"OVER",SUM(B6:G6)*(0.173))

 

[Carrie]

I need to formula to show the calculation even if it doesn't = $711.03, but
if the calculation equals more than $711.03 I would like it to only show
$711.03 and no more.

 

[cm]

this will give you that result:

=if((SUM(B6:G6)*(0.173))>=711.03,711.03,(SUM(B6:G6)*(0.173)))

 

[Carrie]

No, this still doesn't work.......................

 

[Carrie]

I would like the result to show even if it only equals $12 but the max amount
I want it to show is $711.03 - even if it equals more.

 

[Gord Dibben]

=MIN(SUM(B6:G6)*(0.173),711.3)


Gord Dibben MS Excel MVP

 

[Carrie]

Still doesn't work, the calculation is coming up as 711.03 when it should be
121.12

 

[Fred Smith]

Then your data is bad. What does sum(b6:g6) equal?

Regards,
Fred.

 

[Gord Dibben]

Works for me.

Enter 200 in each of cells B6:G6

What do you get returned from the formula?

I get 207.6

Enter 1000 in each of those cells.

I get 711.03..........without the MIN I get 1038

Could be your original data is incorrect.


Gord

 

[Carrie]

Not sure what you mean by bad data.

The sum for the 3 lines are as following

2,623.50(b), 60.75 (c), 40(f) = 2724.25 (there's 2 blank colums inbetween)

I selected all the colums for accounting format. I have Excel 2007

Help!!

 

[Gord Dibben]

Assuming your numbers are in B6, C6, and F6

In H6 enter.........

=SUM(B6:G6) returns 2724.25

=SUM(B6:G6)*0.173 returns 471.2953

=SUM(MIN(B6:G6)*(0.173),711.3) returns 471.2953


Gord