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

## Files

• 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!

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

I knew I was close! Thanks for the help - this did the trick ## Participate now!

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