If/Then type formula

Kyle Hicks

I have three percentages that are fed in a sheet summary. I can write this formula in the same sheet summary so there is no reason for cross sheet references.

I need to write a formula that calculates the median of the three percentages. The only issue is that some of the percentages based upon the goals are beyond 100%.

The goals are:

Overhead: 56

Substation: 84

Warehouse: 8

These are calculated using a CountIf function.

I need to cap the formula to only calculate up to 100% to ensure we do not begin to believe that we are exceeding our goals in every section.

What are your thoughts?

I have attached a photo for reference.

Kyle

Genevieve P.

Hi @Kyle Hicks

You can add an IF statement around your formulas to say that if the output is over 1, return 1, otherwise complete the formula.

Try something like this:

=IF(Formula > 1, 1, Formula)

If this doesn't make sense, it would be helpful to know what formulas you're exactly using, and I can help update them with the structure above.

Cheers!

Genevieve

Kyle Hicks

Genevieve,

My current formula is:

=MEDIAN([Overhead Completion %]#, [Substation Completion %]#, [Warehouse Completion %]#)

But this is returning 109% because:

Overhead Completion %= 109%

Substation Completion %= 95%

Warehouse Completion %= 125%

But if they all capped at 100%, the median would be 98.3 repeating or 98%.

I tried adding the IF function and am failing. I have never used this before.

=IF(Overhead Completion %>1, 1 =MEDIAN([Overhead Completion %]#, [Substation Completion %]#, [Warehouse Completion %]#))

Kyle

Genevieve P.

Hi @Kyle Hicks

We'd want to put the IF statement around each of the other formulas, in the Overhead Completion, Substation Completion, and the Warehouse Completion cell.

You could potentially write it into your overall one, but then the other cells would still show over 100%, is that ok?

If so, try something like this:

=MEDIAN(IF([Overhead Completion %]# >1, 1, [Overhead Completion %]#), IF([Substation Completion %]# >1, 1, [Substation Completion %]#), IF([Warehouse Completion %]# >1, 1, [Warehouse Completion %]#))

Cheers,

Genevieve

Kyle Hicks

Genevieve,

This is still returning 100%. I am not sure what tweaks to make.

Genevieve P.

Hi @Kyle Hicks

The formula is translating your numbers into the following:

Overhead Completion %= 100%

Substation Completion %= 95%

Warehouse Completion %= 100%

This means that the MEDIAN is 100%.

Are you perhaps looking for the Average, instead?

Try:

=AVG(IF([Overhead Completion %]# >1, 1, [Overhead Completion %]#), IF([Substation Completion %]# >1, 1, [Substation Completion %]#), IF([Warehouse Completion %]# >1, 1, [Warehouse Completion %]#))

Kyle Hicks

Genevieve,

Wow, facepalm.

I was so focused on the formula itself, I missed the actual meaning behind the functions.

You folks never cease to amaze me.

Thanks!

Kyle

Genevieve P.

Haha no problem at all! I'm glad we were able to get there in the end. 🙂