|
| Author | Thread |
|
|
09/04/2008 05:54:39 PM · #1 |
I love Excel. There is so much you can do with it and every time I set out to create a spreadsheet I run into a question, "well, how do I do this?" and almost always there is an answer. This is a total finesse question, but let's see if anybody is up to the task.
I have a formula in a cell that calculates a dilution for a vial of antigen. Basically the number will be somewhere between 10 and 1000 (that's not crucial). I would like the cell to display in the manner we are used to seeing dilutions such as "1:100".
The easy solution:
="1:"&(my formula)
Here is the problems:
#1: When I do this, the formatting forgets that I don't want any decimal places so I wind up with something that looks like this:
1:109.0909090 instead of 1:109
Any way to make the formula lop off the decimals? Oooh, I'm off to explore. I have an idea. Let's see if I get the answer before anybody else.
|
|
|
|
09/04/2008 05:57:45 PM · #2 |
| I guess you've already right-clicked and changed the number of decimal places in formatting? |
|
|
|
09/04/2008 06:00:51 PM · #3 |
Put 'int' for integer in front of your parenthesis for 'my formula' ie. "1:"&int(my formula)
This should round your number to the nearest integer.
eta: Nope is just lops off everything behind the decimal. Going to see if there is rnd (rounding function) available.
Message edited by author 2008-09-04 18:03:03. |
|
|
|
09/04/2008 06:04:40 PM · #4 |
| Yes, rounding works also, the example is this: ="1:"&ROUND(A1,0) |
|
|
|
09/04/2008 06:12:22 PM · #5 |
YES! I found the INT command.
OK, problem #2:
Here was my original formula:
=IF(AND(K13<>"")=TRUE,1/(0.5/(0.3*(100/K13))),"")
Because the cell K13 now equals "1:whatever" the formula screws up. So I thought I'd just put the dilutional formula in the place of "k13" in the formula above giving me this:
=IF(AND(K13<>"")=TRUE,1/(0.5/(0.3*(100/(D12/(SUM(F12:H12)))*6))),"")
However, the value seems all whacked out. It went from 0.5 to 18. I can't quite figure out why.
The sum of F12:h12 should be 1.0 in this example.
Ignore the AND stuff. That's basically for some conditional formatting I'm doing.
Message edited by author 2008-09-04 18:12:51. |
|
|
|
09/04/2008 06:20:27 PM · #6 |
| Don't worry. I fixed it with another set of (). That formula is quite ugly and I bet I could clean up the constants... :) |
|
|
|
09/04/2008 06:29:52 PM · #7 |
| Glad you got it figured out! |
|
|
|
09/04/2008 06:41:30 PM · #8 |
If you think that is bad Here is one of the codes for calculating the scores for DPCO...
=IF(RANK(C14,(C$14,C$26,C$38,C$50,C$62,C$74,C$86,C$98,C$110,C$122,C$134,C$146,C$158,C$170,C$182,C$194),0)=1,"16.97",IF(RANK(C14,(C$14,C$26,C$38,C$50,C$62,C$74,C$86,C$98,C$110,C$122,C$134,C$146,C$158,C$170,C$182,C$194),0)=2,"15.87",IF(RANK(C14,(C$14,C$26,C$38,C$50,C$62,C$74,C$86,C$98,C$110,C$122,C$134,C$146,C$158,C$170,C$182,C$194),0)=3,"14.77",IF(RANK(C14,(C$14,C$26,C$38,C$50,C$62,C$74,C$86,C$98,C$110,C$122,C$134,C$146,C$158,C$170,C$182,C$194),0)=4,"13.67",IF(RANK(C14,(C$14,C$26,C$38,C$50,C$62,C$74,C$86,C$98,C$110,C$122,C$134,C$146,C$158,C$170,C$182,C$194),0)=5,"12.57",IF(RANK(C14,(C$14,C$26,C$38,C$50,C$62,C$74,C$86,C$98,C$110,C$122,C$134,C$146,C$158,C$170,C$182,C$194),0)=6,"11.47",IF(RANK(C14,(C$14,C$26,C$38,C$50,C$62,C$74,C$86,C$98,C$110,C$122,C$134,C$146,C$158,C$170,C$182,C$194),0)=7,"10.37",IF(RANK(C14,(C$14,C$26,C$38,C$50,C$62,C$74,C$86,C$98,C$110,C$122,C$134,C$146,C$158,C$170,C$182,C$194),0)=8,"9.27",IF(RANK(C14,(C$14,C$26,C$38,C$50,C$62,C$74,C$86,C$98,C$110,C$122,C$134,C$146,C$158,C$170,C$182,C$194),0)=9,"8.17",IF(RANK(C14,(C$14,C$26,C$38,C$50,C$62,C$74,C$86,C$98,C$110,C$122,C$134,C$146,C$158,C$170,C$182,C$194),0)=10,"7.01",IF(RANK(C14,(C$14,C$26,C$38,C$50,C$62,C$74,C$86,C$98,C$110,C$122,C$134,C$146,C$158,C$170,C$182,C$194),0)=11,"6.02",IF(RANK(C14,(C$14,C$26,C$38,C$50,C$62,C$74,C$86,C$98,C$110,C$122,C$134,C$146,C$158,C$170,C$182,C$194),0)=12,"5.03",IF(RANK(C14,(C$14,C$26,C$38,C$50,C$62,C$74,C$86,C$98,C$110,C$122,C$134,C$146,C$158,C$170,C$182,C$194),0)=13,"4.04 ",IF(RANK(C14,(C$14,C$26,C$38,C$50,C$62,C$74,C$86,C$98,C$110,C$122,C$134,C$146,C$158,C$170,C$182,C$194),0)=14,"3.05",IF(RANK(C14,(C$14,C$26,C$38,C$50,C$62,C$74,C$86,C$98,C$110,C$122,C$134,C$146,C$158,C$170,C$182,C$194),0)=15,"2.06",IF(RANK(C14,(C$14,C$26,C$38,C$50,C$62,C$74,C$86,C$98,C$110,C$122,C$134,C$146,C$158,C$170,C$182,C$194),0)=16,"1.07",))))))))))))))))
And which btw I can't call more than 8 If statements in one cell, anyone know how to call more?
|
|
|
|
09/04/2008 08:46:48 PM · #9 |
Sorry DD I don't think it can be done without writing your own VBA function
Max Nested If in Excel |
|
|
|
09/04/2008 08:48:00 PM · #10 |
Originally posted by Dirt_Diver:
If you think that is bad Here is one of the codes for calculating the scores for DPCO...
=IF(RANK(C14,(C$14,C$26,C$38,C$50,C$62,C$74,C$86,C$98,C$110,C$122,C$134,C$146,C$158,C$170,C$182,C$194),0)=1,"16.97",IF(RANK(C14,(C$14,C$26,C$38,C$50,C$62,C$74,C$86,C$98,C$110,C$122,C$134,C$146,C$158,C$170,C$182,C$194),0)=2,"15.87",IF(RANK(C14,(C$14,C$26,C$38,C$50,C$62,C$74,C$86,C$98,C$110,C$122,C$134,C$146,C$158,C$170,C$182,C$194),0)=3,"14.77",IF(RANK(C14,(C$14,C$26,C$38,C$50,C$62,C$74,C$86,C$98,C$110,C$122,C$134,C$146,C$158,C$170,C$182,C$194),0)=4,"13.67",IF(RANK(C14,(C$14,C$26,C$38,C$50,C$62,C$74,C$86,C$98,C$110,C$122,C$134,C$146,C$158,C$170,C$182,C$194),0)=5,"12.57",IF(RANK(C14,(C$14,C$26,C$38,C$50,C$62,C$74,C$86,C$98,C$110,C$122,C$134,C$146,C$158,C$170,C$182,C$194),0)=6,"11.47",IF(RANK(C14,(C$14,C$26,C$38,C$50,C$62,C$74,C$86,C$98,C$110,C$122,C$134,C$146,C$158,C$170,C$182,C$194),0)=7,"10.37",IF(RANK(C14,(C$14,C$26,C$38,C$50,C$62,C$74,C$86,C$98,C$110,C$122,C$134,C$146,C$158,C$170,C$182,C$194),0)=8,"9.27",IF(RANK(C14,(C$14,C$26,C$38,C$50,C$62,C$74,C$86,C$98,C$110,C$122,C$134,C$146,C$158,C$170,C$182,C$194),0)=9,"8.17",IF(RANK(C14,(C$14,C$26,C$38,C$50,C$62,C$74,C$86,C$98,C$110,C$122,C$134,C$146,C$158,C$170,C$182,C$194),0)=10,"7.01",IF(RANK(C14,(C$14,C$26,C$38,C$50,C$62,C$74,C$86,C$98,C$110,C$122,C$134,C$146,C$158,C$170,C$182,C$194),0)=11,"6.02",IF(RANK(C14,(C$14,C$26,C$38,C$50,C$62,C$74,C$86,C$98,C$110,C$122,C$134,C$146,C$158,C$170,C$182,C$194),0)=12,"5.03",IF(RANK(C14,(C$14,C$26,C$38,C$50,C$62,C$74,C$86,C$98,C$110,C$122,C$134,C$146,C$158,C$170,C$182,C$194),0)=13,"4.04 ",IF(RANK(C14,(C$14,C$26,C$38,C$50,C$62,C$74,C$86,C$98,C$110,C$122,C$134,C$146,C$158,C$170,C$182,C$194),0)=14,"3.05",IF(RANK(C14,(C$14,C$26,C$38,C$50,C$62,C$74,C$86,C$98,C$110,C$122,C$134,C$146,C$158,C$170,C$182,C$194),0)=15,"2.06",IF(RANK(C14,(C$14,C$26,C$38,C$50,C$62,C$74,C$86,C$98,C$110,C$122,C$134,C$146,C$158,C$170,C$182,C$194),0)=16,"1.07",))))))))))))))))
And which btw I can't call more than 8 If statements in one cell, anyone know how to call more? |
It looks prettier in notepad. =^) |
|
|
|
09/04/2008 09:28:49 PM · #11 |
I like to play with excel and have a good bit of experience hacking some complicated spreadsheets together.
So even though you already have it figured out, I couldn't resist throwing in my $0.02 worth.
Here is another way using the CONCATENATE function
the Concatenate function allows you to join 2 or more strings together.
The syntax for the Concatenate function is:
Concatenate( text1, text2, ... text_n )
There can be up to 30 strings that are joined together.
=CONCATENATE("1:",INT(A1+B1+C1/A2))
replace the INT(A1+B1+C1/A2) with your function
Here is one of the many references I've found for excel and where I copied the concatenate reference info from //www.techonthenet.com/excel/index.php
|
|
|
|
09/06/2008 09:34:14 PM · #12 |
Originally posted by BrianM:
I like to play with excel and have a good bit of experience hacking some complicated spreadsheets together.
So even though you already have it figured out, I couldn't resist throwing in my $0.02 worth.
Here is another way using the CONCATENATE function
the Concatenate function allows you to join 2 or more strings together.
The syntax for the Concatenate function is:
Concatenate( text1, text2, ... text_n )
There can be up to 30 strings that are joined together.
=CONCATENATE("1:",INT(A1+B1+C1/A2))
replace the INT(A1+B1+C1/A2) with your function
Here is one of the many references I've found for excel and where I copied the concatenate reference info from //www.techonthenet.com/excel/index.php |
CONCATENATE worked for something else I needed it for thanks for that.
Message edited by author 2008-09-06 21:36:32.
|
|
|
|
Current Server Time: 08/02/2025 03:16:24 AM  |
Home -
Challenges -
Community -
League -
Photos -
Cameras -
Lenses -
Learn -
Help -
Terms of Use -
Privacy -
Top ^
DPChallenge, and website content and design, Copyright © 2001-2025 Challenging Technologies, LLC.
All digital photo copyrights belong to the photographers and may not be used without permission.
Current Server Time: 08/02/2025 03:16:24 AM EDT.
|
