If/Then type formula

✭✭✭
edited 12/06/21

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:

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.

I have attached a photo for reference.

Kyle

Tags:

• Employee

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

October 8 - 10, Seattle, WA | Register now

• ✭✭✭

Genevieve,

My current formula is:

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

But this is returning 109% because:

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

• Employee

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

October 8 - 10, Seattle, WA | Register now

• ✭✭✭

Genevieve,

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

• Employee

The formula is translating your numbers into the following:

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 %]#))

October 8 - 10, Seattle, WA | Register now

• ✭✭✭

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

• Employee

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