Formula to delete but maintain counts after the maximum has been reached

wheddingsjr · #1 01-03-2018, 01:22 PM

Hi all

I have a spreadsheet that contains four columns of data (see attached). Column ‘C’ is a formula that tallies the numbers in column ’B’. The accumulation is based on the acct# in column ‘A’. Each time the Acct# changes the count starts again.
Column ‘E’ is a formula that adds column ‘D’ & ’B’. The formula that I don’t have but need is for column ‘D’. Is there a formula that can be written that looks at column ‘C’ to determine if that number exceeds “12” and if so by how much? (see sample in column D). So, at the end of the day, column ‘C’ will have the tallied total, column ‘D’ will have the number of units over by line, and column ‘E’ will have the maximum of 12 units. Obviously the two columns added would equal column ‘C’. I have been manually going through 30k+ lines manually doing this and if there is a formula that could help me that would be greatly appreciated.

Thanks

NBVC · #2 01-03-2018, 01:38 PM

In D2, try:

=IF(C2<=12,"",-(MIN(B2,C2-12)))

or

=-MAX(0,(MIN(B2,C2-12))) with custom format 0;[Red](0);;@

If you choose first formula, then in order not to get #VALUE! error in column E, change formula in E2 to:

=SUM(D2,B2)

both copied down

wheddingsjr · #3 01-03-2018, 01:49 PM

WOW!!!

Thanks NBVC. That was incredibly easy and worked fantastically. I appreciate your assistance

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Calculate counts between fluid rows - TIME	rgauss	Excel	3	01-18-2017 04:09 PM
Word 2011 error in character counts	Arbor Friend	Word	0	12-17-2016 12:56 PM
How to maintain formula while using Merge and Centre command	Aditya M	Excel	1	04-11-2016 11:25 PM
Maximum number of working days reached= warning color	perZZon	Excel Programming	1	05-09-2014 03:01 PM
Delete Formula	ibrahimaa	Excel	1	06-24-2011 09:17 AM