A count of possibilities

[Shtanto]

Greetings all,

Let's say I work at a company that builds machines with different model numbers. The most basic machine we build has 2 components. The most advanced has 6. Of these two most advanced types, we have 2 categories, A and B, so we make a super fancy A and a super fancy B. The store room has all the parts in it. I keep a spreadsheet with numbers of everything I have What I need is a way of calculating how many machines of every type I can make with the parts I have available.
Machines can have between 2 and 6 components depending on how fancy they are. There are two types of component that can make a 5 component machine into a 6 component machine. This means there are 7 component types, and we make 6 different types of machine. All machines will always have components 1 and 2. They don't get any more basic than that. As we add components numbered 3, 4 and 5, the machine gets fancier, until we add component 6A or 6B to make a super fancy.
If I have a limited supply of a low numbered part (say a 3, 4 or 5) then I won't be able to make many machines. Can excel keep track of the types of machine I can make and how many of each I could make given what I have available? I have the nested IF statement all done.

=IF(B22+C22+D22+E22+F22+G22+H22=6,"Blended (ACD 1)",(IF(B22+C22+D22+E22+F22+G22+H22=6,"Blended (ACD 2)",(IF(B22+C22+D22+E22+F22+G22+H22=5,"Outbound",(IF(B22+C22+D22+E22+F22+G22+H22=4,"Inbound with Scripter",(IF(B22+C22+D22+E22+F22+G22+H22=3,"Inbound",(IF(B22+C22+D22+E22+F22+G22+H22=2,"Business User","Check this cell")))))))))))

Thanks,
Shtanto

 

[konew1]

Hi
Your formula will never return "Blended (ACD2)" because the condition has already been assessed as true and returned "Blended ACD1"

Do you want the maximum number of machines that can be built? That is just the minimum of component1 and component 2.
If you make a bunch of basis machines and use up you base components then it impacts on the ability to make more advanced machines. You need explain more if you want excel to try to calculate something.

Can make base machine =(Min part 1, part2)
can make machine 2 (next advanced ) = min(part1,part2,part3)
can make machine 3 = min (part1,part2,part3,part4)
etc