1. displaying max number

Hello all,

the following formula works great as is. However I want to add the max allowed number accordingly to be displayed if the multiplication results goes over that.

=if(b2>90,vlookup(t3,tables!a4:b6,2)

years multil max
.24 .0577 120
5 .0769 160
10 .0961 200

Thanks all for the help, much appreciated

2. explaing more !!

Hi all.

I am not sure if I have explained this well enough in the previous post.

I want to ADD a function to the existing formula, but I do not know which or if there is one, to give the result of the multiplication up to the max listed in the table by year, then display the max number only. I hope this clears up my request!

thanks again

3. cvbs,

I don't see any multiplication that you want to compare the max value to? Please explain further.

4. RG,

Sorry .... to many things going on at once ...
=if(b2>90,vlookup(t3,tables!a4:b6,2)*A10 in the cell A10 is the running total number of hours that the person worked so far this year. So it would multiply that amount times the multiplier (based on then number of years the person has worked) and come up with a result. This formula does this as written.... what I want to do is have the max number shown if the calc. would result to a larger number. So what do I have to add to this formula to accomplish this feat???

Thanx,

5. This should do the trick, it's long because of the second test. Note: since I don't have your workbook this is AIR code {untested}.
=if(b2>90,If(vlookup(t3,tables!a4:b6,2)*A10>=vlook up(t3,tables!a4;b6,3),vlookup(t3,tables!a4;b6,3),v lookup(t3,tables!a4:b6,2)*A10),{place what to do if b2 <=90 here})

6. is the >= (and the rest of the formula) to be added to the front part??? or are you saying Start with the =vlookup and forget the portion I already have????

7. I suspect the simplest option is something like wrap the formula you have in min:

=MIN(100,if(b2>90,vlookup(t3,tables!a4:b6,2)*A10))

This returns the smaller of 100 or your formula, which I think is what you want - it can't return more than 100 in this case. No promises I got all the brackets right It's counter intuitive at first glance to look at the MIN function to set a max, but it's what you want

8. Originally Posted by cvbs
is the >= (and the rest of the formula) to be added to the front part??? or are you saying Start with the =vlookup and forget the portion I already have????
Copy the whole formula as written.

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•