Adding data to create ranges

[Guest]

Hello!
I want to calculate the number of bagtags in my excel sheet and have the
result come out in ranges. Can you please help me create a formula for this?
Note: a cell may also have a value of 0. I would like the formula to
result in a blank cell when the value is zero.
Sample:
Would like formula to create range
Sacks Bagtags
24 1-24
1 25
6 26-31
27 32-58
5 59-63
5 64-68
5 69-73
32 74-105
1 106

 

[Guest]

Try this formula:

=IF(A1="","",SUM($A$1:A1)-A1+1&IF(A1=1,""," - "&SUM($A$1:A1)))

Copy down as needed.

HTH,
Elkar

 

[Guest]

Thank you!
The formula worked for cells that had values greater than zero. (Yeah!!)
Is there a way I can get the result to leave a blank cell if the value is
zero?

 

[Guest]

Ah yes, I didn't condiser that. Its a simple fix though.

=IF(OR(A1="",A1=0),"",SUM($A$1:A1)-A1+1&IF(A1=1,""," - "&SUM($A$1:A1)))

HTH,
Elkar

 

[Guest]

Perfect! Thanks so much!
--
Jodie Gardner

Ah yes, I didn't condiser that. Its a simple fix though.

=IF(OR(A1="",A1=0),"",SUM($A$1:A1)-A1+1&IF(A1=1,""," - "&SUM($A$1:A1)))

HTH,
Elkar