Forum Discussion
Solved
Formula Help
Hello. I am a Novice Excel user.
Here is my formula. I can get it to return a "2" but it always returns a "0" instead of a "1"
=IF(OR(AND(C4>10,D4>8),C4<18,D4<16),1)*(IF(OR(C4>=18,D4>=16),2))
I work in Building Automation and I am trying to make a table which I can enter a number of inputs and outputs and it will return a value of how many controllers I need.
One controller I always require has 10 inputs and 8 outputs. This is why in my formula, I subtract those values.
Each additional controller has 8 inputs and 8 outputs.
I would like to return values of "1,2,3,4,5" etc (number of controllers) based off of how many inputs and outputs I enter.
Essentially,
10 inputs, 8 outputs = 0 controllers
11-18 inputs, 9-16 outputs = 1 controller
19-26 inputs, 17-24 outputs = 2 controllers
etc...
Hopefully this makes sense what I am trying to accomplish.
Thank you in advance!
- =MAX(ROUNDUP((C4-10)/8,0),ROUNDUP((D4-8)/8,0),0)
just added the ,0 at the end of the max list. that should work
7 Replies
An alternative is to use INDEX and MATCH with a table such as the attached. This table could easily be extended; I only took it this far to illustrate the concept.
As is always the case (as your now two replies illustrate) there are always multiple ways in Excel to get from point A to point B.
=IF(SMALL(IF(D2<=$I$2:$I$18,ROW($I$2:$I$18)-1),1)=SMALL(IF(E2<=$J$2:$J$18,ROW($J$2:$J$18)-1),1),SMALL(IF(E2<=$J$2:$J$18,ROW($J$2:$J$18)-1),1)-1,"")
You can apply this formula along with a reference table. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel for the web or Excel 2021. The formula returns an empty cell if there isn't a match however i'm not sure if this is possible in your scenario.
Thanks OliverScheurich
I did try this, and if the outputs match the inputs it works but like you said if they don't match I get no value.
That is why initially I was trying out a lot of IFs and ANDs and ORs as well as less than/greater than and equal to.
Ideally, I would like to be able to input any arbitrary input/output value and the formula would still input the controller count value.
Thank you for your response!
- why can't you do something like:
=MAX(ROUNDUP((C4-10)/8,0),ROUNDUP((D4-8)/8,0))
