functionif-statementgoogle-sheetsnested-ififs

how to function this ifs

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?

Solution

  • 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", )))