Cap number and make new table

[macbookboy]

Sorry for posting in the wrong section (if it is)

I am trying to cap a number in excel and make it create a new table... i am not very good at explaining as you will see...

Tims ice cream van:
Maximum ice creams he can hold 32

So if someone orders 64 ice creams, excel will say

32 Ice creams per van maximum - Solution 2 vans in a new table.

e.g.

Ice creams wanted = 32
Ice cream vans it will take 1

Ice creams wanted = 64
Ice cream vans it will take 2

ice creams wanted = 65
Ice cream vans it will take 2, 1 in glove box

ice creams wanted = 97
Ice cream vans it will take 3, 1 in glove box

I hope someone can help me with a formula to make this work!!!

thanks

[tigeravatar]

macbookboy,

Attached is an example workbook based on the criteria you described. Is something like that what you're looking for?

[macbookboy]

Pricing Sheet - SIM CARDS.xlsxHi Tiger,

I have uploaded what I am working on, so you get a better picture.

The biggest gateway we own is a 32Engine Gateway, if someone wants 50,000 SMS (use the table) the engines required is 41, but the maximum we can do is 32, so we would need the table to create a new section which says you will need 2x 32 Engines

Thanks again tiger for the help.

[tigeravatar]

macbookboy,

I'm not sure what you mean by "need the table to create a new section"
Attached is a modified version of your example workbook.
I inserted a new row (row 12), which contains this formula in cell E12:
=INT(CEILING(E4/B3,4)/128)+1

That formula shows the number of 32 Engines needed to accomadate the No. of SMS entered. So with the 500,000 SMS example, the formula returns the number 2.

[macbookboy]

Pricing Sheet - SIM CARDS (Modified by tigeravatar & macbookboy) (2).xlsxPricing Sheet - SIM CARDS.xlsxThat is fantastic tiger!!

just what i was looking for

What i mean by having a new "box" When 500,000 was input it gave 2 x 32 sims, now the 32 (is a product) shall we say in the case GSM Gateway 7000, i wanted it to take me to a box which would list product information in, so basically the whole point in the table is for our business to enter a value (SMS) and it will give us all the information in the box and (ideal) product what matches the figures ?

Again thanks for the help, if you can help further that would be great, I am a very novice user

I have uploaded just the desgin, but i am not sure how to link it, see the attachment.

[tigeravatar]

macbookboy,

Attached is what I think you're looking for. Columns N:R have been reserved for box title and information lines.
Cell G2 has a formula in it that will pull the correct title depending on number of 32 engines needed. If there is only 1 engine needed (e.g., SMS = 300,000), it pulls the first box information. If 2 engines needed (e.g., SMS = 600,000), it pulls the 2nd box information, etc. Here's the formula:
=IF(OR(E12="",E12<1),"",INDEX(N2:R2,,E12))


In cell G4 and copied down is this formula that pulls box text information based on the title:
=IF($G$2="","",INDEX($N$2:$R$21,ROW(3:3),MATCH($G$2,$N$2:$R$2,0)))


G4:G21 has a conditional formatting applied so that if a cell = 0, it is formatted to Custom format ";;;" so that the 0's will be hidden. Columns N:R can be hidden if desired. Is something like that what you're looking for?