Formula help - coupon #

**rhenry2424** · 10-03-2013, 03:42 PM

I need help with a formula. I have highlighted it in the attachment.
Here is the jist..... There are 8 apartment buildings and 15 apartments in each building.
Coupon books need to be made for each apartment & each month.
I am having a problem with the coupon number. It works for the Building 1, apartment 1, and months 1-12, but when I go to the next apartment, instead of going to the next number, it goes to 2.

(Current formula is Building # * Apartment # * Month #)

EX. Building 1
Apartment 1
Month 8
= coupon 8

Building 1
Apartment 2
Month 1
= coupon 2
SHOULD BE 13
(coupon numbers should keep going up, and not repeat)

There will be 1440 coupons numbered 1-1440.

Yes, this is for a school work problem, but not homework, I promise. It is a problem we are working on in class and the professor could not figure it out and has tasked me into figuring it out.
Personally, I would probably do 1-2-1 as the coupon number that way I could quickly see the building #-Apartment #- Month, but since that is not how he wants the problem solved, I guess I need to do it his way.

Thank you for any help you can provide.

A6.xls

**Pauleyb** · 10-03-2013, 04:15 PM

I really don't know what to say. If this teacher is for math, they need to find another line of work. While the solution is not super-obvious, it should be within the realm of a math teacher. I'm not trying to be a jerk, but ... wow.

Essentially, you need to skip 12 for every apartment, so that is a multiplier used against apartment numbers. You'll need another muliplier for building. Use those multipliers against your variables (building and apartment) and sum.

**popipipo** · 10-03-2013, 04:17 PM

Try this one:

Please Login or Register  to view this content.

**alansidman** · 10-03-2013, 04:21 PM

A nested if statement for the coupon number will work

I have only done the first three buildings, but you should get the idea

=IF(C7=1,C7*C8*C9,IF(C7=2,180+(C8*C9),IF(C7=3,360+(C8*C9))))

Alternatively, you could do the same thing with a VBA statment using Case Select

**rhenry2424** · 10-03-2013, 04:59 PM

Thank you both for the solutions. I used the one from popipipo and it worked, so I am not moving forward with the one from alansidman. I am saving it for future use though. Thanks again.

Also, this is not for a math class, it is for a computer class.