Forum Discussion

Copper Contributor

Jan 17, 2024

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)) ...

Formulas and Functions

mtarler
Jan 18, 2024
=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

OliverScheurich

Gold Contributor

Jan 17, 2024

Darren403

=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.

Darren403
Copper Contributor
Jan 17, 2024
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!
- mtarler
  Silver Contributor
  Jan 17, 2024
  why can't you do something like:
  =MAX(ROUNDUP((C4-10)/8,0),ROUNDUP((D4-8)/8,0))
  - Darren403
    Copper Contributor
    Jan 18, 2024
    mtarler THANKS!
    
    This seems to do the trick. However, if I have blank columns/rows it seems to return a "-1". Any alteration to this you can think of? I have a formula at the bottom to calculate totals of each controller but with the negative values I am getting negative totals.