Logical Function - Math results using text and numeric data

  • I'm close to getting my desired result, however, I'm stuck on one little part. I'll use row 7 as the example.


    Attached sheet.


    The cells in Column P can be one of three things based on a validation list: Aggregate, 70%, and 75%. I could use 100% and fix this whole little dilema, but Dealer Participation (Column Q) can never be 100%.


    Q7, when the cell data in P7 = 70% or 75% gives a numeric result. However, one of the nested IF statements performs math against one of the data types in P7 that is text based - resulting in a #VALUE! error.


    In a nutshell, what I'm trying to accomplish is the following. I think I'm close on my formula:


    Q7 = W7+O7 if P7 = "Aggregate"
    Q7 = W7 * P7


    Remember, P7 can equal 70%, 75% or Aggregate. The formula bombs out when P7 equals "Aggregate".


    Thanks in advance.

  • Re: Logical Function - Math results using text and numeric data


    According to the example above...


    =IF($P7="Aggregate",$W7+$O7,$W7*$P7)


    According to the formula in your sample file...


    =IF($P7="Aggregate",$W7+$O7,IF($W7*$P7=0,$O7,IF($W7*$P7>0,$W7*$P7)))


    Actually, the following should suffice...


    =IF($P7="Aggregate",$W7+$O7,IF($W7*$P7=0,$O7,$W7*$P7))


    Hope this helps!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!