对公式应用多个条件 [英] Applying multiple conditions to formula

问题描述

我有一种情况，我需要对几个不同的起始位置应用几个价值边界，然后输出持续的位置。

价值边界是：< 500> = 500< 750> 750< = 850> 850

起始位置是基本标准，标准+和白金，

然后应用价值边界并输出持续定位，

例如一个以基本开始的人达到600，然后移动到标准，或者白金得到700的人回复到标准+等等，我已经尝试了一个嵌套的IF无效。

编辑：似乎解决方案超出了一个公式，我需要VBA才能解决这个问题？

解决方案

我很难得到一个答案，但最终在我能够解决的建议的帮助下，是完成的公式：

  = IF（AND（H4 =Basic，G17 <450），Basic （AND（H4 = 基本，G17> = 450）， 标准，IF（AND（H4 = 标准，G17< 450）， 基本，IF（AND（H4 = 标准，G17> 449，G17< 700）， 标准，IF（AND（H4 = 标准，G17> = 700）， 标准+，IF（AND（H4 = 标准+，G17> 699，G17< 800）， 标准+，IF（AND（H4 = 标准+，G17< 700）， 标准，IF（AND（H4 = 标准+，G17> = 800）， 铂，IF（AND（H4 =铂金，G17 <800），标准+，白金））））））））））
  

I have a situation where I need to apply several "value boundaries" to several different "starting positions" and then subsequently output the "ongoing position".

The "value boundaries" are: <500, >=500<750, >750<=850 and >850

The "starting positions" are "Basic", "Standard", "Standard+", and "Platinum",

The value boundaries are then applied and the "ongoing position" outputted,

e.g. a person starting at "Basic" achieves 600 and then moves to "standard", or someone at "Platinum" gets 700 so reverts to "Standard+" etc etc, I have tried this with a nested IF to no avail .

Edit: it seems the solution is beyond a formula do I need VBA to solve this one?

解决方案

I struggled to get an answer to this but eventually with the help of the suggestions I was able to work it out, here is the finished formula:

=IF(AND(H4="Basic",G17<450),"Basic",IF(AND(H4="Basic",G17>=450),"Standard",IF(AND(H4="Standard",G17<450),"Basic",IF(AND(H4="Standard",G17>449,G17<700),"Standard",IF(AND(H4="Standard",G17>=700),"Standard+",IF(AND(H4="Standard+",G17>699,G17<800),"Standard+",IF(AND(H4="Standard+",G17<700),"Standard",IF(AND(H4="Standard+",G17>=800),"Platinum",IF(AND(H4="Platinum",G17<800),"Standard+","Platinum")))))))))

这篇关于对公式应用多个条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！