1

I'm new to formulas in google sheets but doing ok with online learning. I have come across something I need to do that I cant find a solution for.

I have this that works:

=if(O13>=Q14,O13-INFO!L5*O13,0)

But now I need to also include a cap to the returned value so if it reaches a certain number it will stop and return no more than a value found in another cell (in this case it would be "INFO!M5").

Is this possible within the same formula and if this type of thing has a certain name for phrase I don't know, is that why I cant find help on it.

Thanks Adam

player0
  • 124,011
  • 12
  • 67
  • 124
Adam
  • 59
  • 8

1 Answers1

1

try nested-if:

=IF(O13>=Q14, IF((O13-INFO!L5*O13)<=INFO!M5, O13-INFO!L5*O13, INFO!M5), 0)

update:

=IF((G7>=0)*(G7<=I7), G7-G7*K7, M7 )

enter image description here

player0
  • 124,011
  • 12
  • 67
  • 124
  • 1
    Hi Thanks, The capping worked but its made me realise the formula I had written wasn't doing exactly what I thought it was up to that value so I need to go back and look at it. Thanks for your help so far though! – Adam Mar 15 '22 at 09:24
  • I looked at nesting but couldn't get my head round it, but I wasn't using spaces inside, does that make a difference? – Adam Mar 15 '22 at 09:26
  • @Adam if you could share a copy/sample of your sheet with example of desired result I'm happy to help – player0 Mar 15 '22 at 09:28
  • 1
    thanks, I just need to relook at what I'm trying to do, I think I've approached it in the wrong way. So will come back with the sheet when I have got my head around what it is I'm wanting. thanks – Adam Mar 15 '22 at 09:33
  • Hi I have a link to the sheet here with what I have got so far: https://docs.google.com/spreadsheets/d/1QiKAmQXsmeZAOtl2jyMKhrA1Isz1dq1w4jQC-r6-yIU/edit?usp=sharing – Adam Mar 15 '22 at 10:38
  • @Adam answer & your sheet updated – player0 Mar 15 '22 at 10:46
  • Hi thanks, the result is showing what the 97% of the value is rather than the result. IE if the sum total is 2100 the value should show 63 after the 97% has been removed, does that make sense? – Adam Mar 15 '22 at 10:52