1

I need help. Been finding the correct function but I don't really know how to explain in words what I'm finding, not sure if its the right function too but

if A1 is 300 or less, B1 will be an amount equals to A1, so C1 will be A1-B1
if A1 is 301-599, B1 will input 300, C1 will be A1-B1
if A1 is 600 or more, B1 will be 0, C1 will be A1/2

so example,
A1 is 100, B1 will be 100, C1 will be A1-B1 = 0
A1 is 400, B1 will be 300, C1 will be A1-B1 = 100
A1 is 700, B1 will be 0, C1 will be a sum of 700/2

not sure if im even making any sense or if its possible

this is what i've tried

=If(A1 < 300, "100%", IF(A1 > 301,"300",if(A1 < 599,"300",if(A1 > 600,"0",))))

it shows
299 or less = 100%
300 above = 300

is there a way for the B1 = 100% for it to show the same value as A1
how to i write the value 300-599 so that anything above 600 is 0

example


=IF(A1< 301, "???",
IF((A1> 301) * (A1< 600), "300",
IF(A1 > 600, "0",)))

how do i get the ??? to be the same value as A1?

player0
  • 124,011
  • 12
  • 67
  • 124
Dark3m0z
  • 13
  • 4
  • I'm not following up with the conditions... `B1 will input a -100%` B1 has an initial value that will be subtracted depending on the value of A1? Can you share a sample Sheet file with the expected output for the given values on A column? – Gustavo Apr 28 '22 at 09:40
  • i've updated the info , had some errors. i wanted B1 to show the same value as A1 if the value of A1 is <300 because in C1, its going to be A1-B1 – Dark3m0z Apr 28 '22 at 10:05

1 Answers1

1

use in B1:

=IF(A1<=300,           {A1-100, 0}, 
 IF((A1>300)*(A1<600), {A1-300, A1-A1-300},
 IF(A1>=600,           {0, A1/2}, )))

enter image description here

update 1:

=IF(A1<=300,           {-100, A1-100}, 
 IF((A1>300)*(A1<600), {-300, A1-300},
 IF(A1>=600,           {0, A1/2}, )))

see: https://webapps.stackexchange.com/questions/123729/multiple-if-statements-with-between-number-ranges-alternative

update 2:

how do i get the ??? to be the same value as A1?

=IF(A1< 301, A1,
 IF((A1> 301) * (A1< 600), "300",
 IF(A1 > 600, "0", )))
player0
  • 124,011
  • 12
  • 67
  • 124