LinkBack URL
About LinkBacks
so im trying to sum the cells C20:F20 while having cell E20 multiplied by cell C4 without having the product exceed 50.
so im trying to sum the cells C20:F20 while having cell E20 multiplied by cell C4 without having the product exceed 50.
Do you mean
=MIN(50,SUM(C20:D20,E20*C4,F20))
or perhaps
=SUM(C20:D20,MIN(50,E20*C4),F20)
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
no... doesnt work, and it would be maximum 50, not minimum. PS im using excel 2000
values would range from 0 to 75
Did you try DO's first formula?
Perhaps you could post an example.
Entia non sunt multiplicanda sine necessitate
i tried both, and they came out as errors...
1st one errors at =MIN(50,SUM(C20:D20,E20*C4,F20))
2nd one errors at =SUM(C20:D20,MIN(50,E20*C4),F20)
What's in the cells?
To repeat, how about posting an example.
Depending on your regional settings, you may need to change teh commas to semicolons.
all numerical values. from c20 to f20Originally Posted by shg
c20=variable by user.
d20=variable by cell. not exceeding 45
e20=variable by user either 0 or 5, multiplied by cell c4 which is between 1 and 15.
f20 is usually empty but is more of a special case. user entered.
Did you change the delimiter as advised ? ie:
Also, as mentioned by shg it is always best to read up on suggestions before dismissing outright based on assumptions, the MIN fn for ex. returns the lesser of the values specified - ie result is essentially "capped" at 50.
Please Login or Register to view this content.
thanks. i tried changing them but whatever i did made it mess up even more. thanks for the help.
Last edited by shg; 01-17-2010 at 07:26 PM. Reason: deleted spurious quote
You're not doing much to help yourself. Post a workbook with an example of what's not working.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks