| Author | Thread |
|
|
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?
|
|
|
|
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.
|
|
|
|
12/09/2014 10:12:26 AM · #3 |
|
|
|
12/09/2014 10:42:15 AM · #4 |
|
|
|
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. :) |
|
|
|
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.
|
|
|
|
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)
|
|
|
|
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. |
|
|
|
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.
|
|
|
|
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. |
|
|
|
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? |
|
|
|
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...
|
|
|
|
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! |
|
|
|
12/10/2014 02:33:08 PM · #14 |
you wish to use Kirbic's hands for a game you want to play?
|
|
|
|
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? |
 |
|
Home -
Challenges -
Community -
League -
Photos -
Cameras -
Lenses -
Learn -
Prints! -
Help -
Terms of Use -
Privacy -
Top ^
DPChallenge, and website content and design, Copyright © 2001-2024 Challenging Technologies, LLC.
All digital photo copyrights belong to the photographers and may not be used without permission.
Current Server Time: 04/19/2024 05:55:42 AM EDT.
