formula help

d0wnt0wn

Well-known Member
Joined
Oct 28, 2002
Messages
771
hi i made this formula base on how much of a certain material a bin can hold..... the problem is that sometimes the formula works out to a .5 1.5 is ok but i dont ever want the total to be just .5 as we will never use less than one bin

=IF(AND(C15<>0,B24="25 SQUARE PER BIN"),ROUNDUP(C15/25,1),IF(AND(C15<>0,B24="20 SQUARE PER BIN"),ROUNDUP(C15/20,1),IF(AND(C15<>0,B24="50 SQUARE PER BIN"),ROUNDUP(C15/50,1),"")))

any way to clean this up and maybe set the minimum number to 1
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Brian from Maui

MrExcel MVP
Joined
Feb 16, 2002
Messages
8,459
Try,

=IF(AND(C15<>0,B24="25 SQUARE PER BIN"),MAX(1,(ROUNDUP(C15/25,1)),IF(AND(C15<>0,B24="20 SQUARE PER BIN"),MAX(1,ROUNDUP(C15/20,1),IF(AND(C15<>0,B24="50 SQUARE PER BIN"),MAX(1,(ROUNDUP(C15/50,1))))))),"")
 

SIXTH SENSE

Well-known Member
Joined
Oct 29, 2003
Messages
1,883
hi!
try this!
Code:
=IF(AND(C15<>0,B24="25 SQUARE PER BIN"),ROUNDUP(C15/25,0),IF(AND(C15<>0,B24="20 SQUARE PER BIN"),ROUNDUP(C15/20,0),IF(AND(C15<>0,B24="50 SQUARE PER BIN"),ROUNDUP(C15/50,0),"")))

I think you should round to 0 and not 1 to have it rounded to the next higher integer value thus 0.5 will be 1
 

d0wnt0wn

Well-known Member
Joined
Oct 28, 2002
Messages
771

ADVERTISEMENT

ahhh ive tried that already.... see the problem is.... after a minimum value of 1 is returned i want the increments to be able to go up .1 at a time.... i just cant have anything below 1
 

tactps

Well-known Member
Joined
Jan 20, 2004
Messages
3,460
What about:

=IF(ISNUMBER(VALUE(LEFT(B24,2))),MAX(ROUNDUP(C15/VALUE(LEFT(B24,2)),1),1),"")
 

d0wnt0wn

Well-known Member
Joined
Oct 28, 2002
Messages
771

ADVERTISEMENT

houstan we have a winner!!! thats the one..... i wonder if you could break that formula down for me
 

tactps

Well-known Member
Joined
Jan 20, 2004
Messages
3,460
First we need to pick up the number to divide by, which is the first 2 characters of the text:
VALUE(LEFT(B24,2))
Note that this will not work if the bin is less than 10 or more than 99 (but the formula can be adjusted if this is required)

Then it looks to see that the first two characters in B24 are a number:
(ISNUMBER(VALUE(LEFT(B24,2)))

If so, then pick up either 1 or the calculated number, whichever is higher:
MAX(ROUNDUP(C15/VALUE(LEFT(B24,2)),1)

If not, then it results in a blank cell ("")

Does that break it down enough for you? Does it make sense?
 

Forum statistics

Threads
1,147,621
Messages
5,742,184
Members
423,710
Latest member
Duarte85

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top