I am looking to do a sumif (I think it should be a Sumif).
See the attached example. Basically I want to sum in a formula a list of numbers, however, if the numbers are greater than the keys max number I want it to take the max number.
So in my example the max value of A is 3, so a value of 4 should be treated as a 3. In my example column J is how I want the numbers to be treated. I'd like to avoid duplicating the table and just have it do these calculations in the formula.
I would think it is beneficial to have the 'adjusted' amount shown for each individual line. Then sum that.
If you just put a formula that results in 9 instead of 15, That would be odd to someone looking at the sheet and sees numbers that add up to 15.
It is not a 'bad thing' to use extra cells to complete a calculation. Excel gives you over 1 million rows and 16 thousand columns.
That's over 17 billion cells. You might as well use a few of them.
In fact, splitting calculations into multiple cells usually makes that calculation more efficient.
Cramming all that calculation into 1 formula will be less efficient.
I have quite a bit of data in the actual spreadsheet, multiple columns and rows, not just one like the example, additionally, the spreadsheet is subject to growth and I would rather have a single formula to avoid any cell referencing concerns that might happen. If a single formula isn't plausible then I can make the other work, just the way this spreadsheet is setup a single formula would be ideal.
Bookmarks