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...


    According to the formula in your sample file...


    Actually, the following should suffice...


    Hope this helps!

Participate now!

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