DPChallenge: A Digital Photography Contest
 

DPChallenge Forums >> General Discussion >> Any Excel experts here?
⏮️⏪◀️ 1/1 ▶️⏩⏭️
Showing posts 1 - 15 of 15, (reverse)
AuthorThread
Canon EOS R5 Mark II
Canon EF 24-105mm f/4.0 L IS
 12/09/2014 09:57:22 AM · #1
I hope this makes sense.

My Excel knowledge is very limited and I'm trying to figure out how to write out this formula:

I have two variables, one would be the projected number of patients that are discharging, and the second would be the actual number that got discharged.

There would be two possible outcomes...if the actual number is lower than the projected, then I would need the percentage of accuracy, so actual/projected. But, if the actual number was greater than the projected, then I would need to get the opposite percentage...projected/actual.

How do I get these two different formulas into one cell?

Nikon D3200
Nikon AF-S DX Nikkor 35mm f/1.8G
 12/09/2014 10:04:59 AM · #2
Originally posted by Cuttooth:
I hope this makes sense.

My Excel knowledge is very limited and I'm trying to figure out how to write out this formula:

I have two variables, one would be the projected number of patients that are discharging, and the second would be the actual number that got discharged.

There would be two possible outcomes...if the actual number is lower than the projected, then I would need the percentage of accuracy, so actual/projected. But, if the actual number was greater than the projected, then I would need to get the opposite percentage...projected/actual.

How do I get these two different formulas into one cell?


There is an Excel function called IF. The syntax is IF(logical_test, [value_if_true], [value_if_false]). The value_if_true and value_if_false can be functions on their own.
Canon EOS-5D Mark II
Tamron SP 24-70mm f/2.8 Di VC USD Canon Mount
12/09/2014 10:12:26 AM · #3
what he said.
Canon EOS-40D
Canon EF 17-40mm f/4.0 L USM
 12/09/2014 10:42:15 AM · #4
Canon EOS R5 Mark II
Canon EF 24-105mm f/4.0 L IS
 12/09/2014 11:02:36 AM · #5
Thanks guys!

I tried the IF function earlier but just couldn't figure out how to write it out.

DPC folks are pure awesomeness. :)
Canon EOS R5
Canon RF 24-70mm f/2.8 L IS
 12/09/2014 03:41:01 PM · #6
In the following formula, the names DistAct and DistProj refer to your variables. Substitute cell addresses as appropriate:

=if(DistAct <= DistProj, DistAct/DistProj, DistProj/DistAct)

ETA: If you name the cells with your actual and projected values as in the above formula, then the formula will work exactly as written. To name a cell, click on that cell, then type the name in the cell address box (at far left of the formula bar where the cell address normally appears)

Message edited by author 2014-12-09 15:43:24.
Pentax K-S2
Sigma 70-200mm f/2.8 EX DG APO IF for Pentax
 12/09/2014 08:15:15 PM · #7
If you don't like the IF statement, you could do the following:

=MIN(DistAct,DistProj) / MAX(DistAct,DistProj)

Canon EOS R5
Canon RF 24-70mm f/2.8 L IS
 12/09/2014 09:19:27 PM · #8
Originally posted by Nobody:
If you don't like the IF statement, you could do the following:

=MIN(DistAct,DistProj) / MAX(DistAct,DistProj)


That's quite elegant! It neatly incorporates the case where the two are equal, using the designed behavior of the MAX and MIN functions.

ETA:
This works too and is even a bit simpler:

=MIN(DistAct/DistProj,DistProj/DistAct)

And perhaps the simplest, made possible by naming the range including the two input cells "Data":

=MIN(Data)/MAX(Data)

Message edited by author 2014-12-09 21:55:19.
Pentax K-S2
Sigma 70-200mm f/2.8 EX DG APO IF for Pentax
 12/09/2014 10:11:56 PM · #9
Originally posted by kirbic:
Originally posted by Nobody:
If you don't like the IF statement, you could do the following:

=MIN(DistAct,DistProj) / MAX(DistAct,DistProj)


That's quite elegant! It neatly incorporates the case where the two are equal, using the designed behavior of the MAX and MIN functions.

ETA:
This works too and is even a bit simpler:

=MIN(DistAct/DistProj,DistProj/DistAct)

And perhaps the simplest, made possible by naming the range including the two input cells "Data":

=MIN(Data)/MAX(Data)


That is very good.

The only flaw I see in any of our solutions is division by zero. To take your last solution one step further:

=MIN(data)/MAX(MAX(data),1)

would suppress any errors.
Canon EOS R5 Mark II
Canon EF 24-105mm f/4.0 L IS
 12/09/2014 10:25:58 PM · #10
holy cow, i really know very little when it comes to excel.

i'll try these when i get back to the office and see what works better.
Canon EOS-5D Mark III
Canon EF 70-200mm f/2.8 L IS USM II
12/10/2014 09:09:52 AM · #11
Ooh these are lovely. Now I know who to ask for far simpler solutions to my own conundrums. I may have a few coming up in the next few days.... Would any of you mind helping if I need the input?
Canon EOS R5
Canon RF 24-70mm f/2.8 L IS
 12/10/2014 11:03:17 AM · #12
Originally posted by pamb:
Ooh these are lovely. Now I know who to ask for far simpler solutions to my own conundrums. I may have a few coming up in the next few days.... Would any of you mind helping if I need the input?


Feel free to PM me if you need a hand...
Nikon D300s
Nikon AF-S Nikkor 70-200mm f/2.8G ED VR II
 12/10/2014 12:05:46 PM · #13
Originally posted by kirbic:
Originally posted by pamb:
Ooh these are lovely. Now I know who to ask for far simpler solutions to my own conundrums. I may have a few coming up in the next few days.... Would any of you mind helping if I need the input?


Feel free to PM me if you need a hand...


Can we make it a game? I wanna play!
Canon EOS-5D Mark II
Tamron SP 24-70mm f/2.8 Di VC USD Canon Mount
12/10/2014 02:33:08 PM · #14
you wish to use Kirbic's hands for a game you want to play?

Nikon D300s
Nikon AF-S Nikkor 70-200mm f/2.8G ED VR II
 12/10/2014 02:48:26 PM · #15
Originally posted by Mike:
you wish to use Kirbic's hands for a game you want to play?


⏮️⏪◀️ 1/1 ▶️⏩⏭️
Current Server Time: 09/27/2026 09:35:10 PM

Please log in or register to post to the forums.
Theme: Classic / Light / Dark
Home - Challenges - Community - League - Photos - Cameras - Lenses - Learn - Help - Terms of Use - Privacy - Top ^
DPChallenge, and website content and design, Copyright © 2001-2026 Challenging Technologies, LLC.
All digital photo copyrights belong to the photographers and may not be used without permission.
Current Server Time: 09/27/2026 09:35:10 PM EDT.