426,139 Members | 1,228 Online
Need help? Post your question and get tips & solutions from a community of 426,139 IT Pros & Developers. It's quick & easy.

# Excel Formula

 P: 47 Hello, I am not good with Excel and I would truly appreciate assistance with the following: Please let me know how I can place all of this in one field, let’s say AN100. If U100 (column U, value in row 100) is smaller than AJ100 than AN100 should be =D100*1 If U100 is smaller than AJ100 and AK100 than AN100 should be =0 If U100 is bigger than AL100 than AN100 should be =D100*3 If U100 is bigger than AL100 and AM100 than AN100 should be =D100*4 If U100 is bigger than AJ100 and AK100, but smaller than AL100 and AM100 cell AN100 should be =D100*2 How can I squeeze all of this into field AN100? (Is there any way to make Excel colour AN cells in different colours, subject to which of the conditions above are met?) Also, I need assistance with the following as well: If U100 (column U, value in row 100) is smaller than AJ100 than AO100 should have value 1 If U100 is smaller than AJ100 and AK100 than AO100 should have value 0 If U100 is bigger than AL100 than AO100 should have value 3 If U100 is bigger than AL100 and AM100 than AO100 should have value 4 If U100 is bigger than AJ100 and AK100, but smaller than AL100 and AM100 cell AO100 should have value 2 Thank you in advance! Feb 25 '09 #1
50 Replies

 Expert Mod 15k+ P: 31,419 Is there any known order of the various values? EG. AJ always greater than AK. Feb 25 '09 #2

 P: 47 Hello NeoPa. AJ is always greater than AK. AL is always smaller than AM. Thank you! Feb 25 '09 #3

 Expert Mod 15k+ P: 31,419 Can AM ever be smaller than AK? Can AL ever be smaller than AJ? Feb 25 '09 #4

 P: 47 Hello. Thank you very much. AM can't be smaller than AK. AL can't be smaller than AJ. Feb 25 '09 #5

 Expert Mod 15k+ P: 31,419 OK. To save asking all the questions in detail, it may be a better idea just to look at the question in post #2 again and specify the exact known relation between AJ, AK, AL & AM. I presume there's a smallest to largest progression, but still have no idea if any values can be equal to the next one up or down. I just need a full answer to this question if you can (in one place and clearly laid out). Feb 25 '09 #6

 P: 47 Hello NeoPa! Thank you for your time. Please note that AJ = Mean minus Standard deviation AK = Mean minus Standard deviation x 2 AL = Mean plus Standard deviation AM = Mean plus Standard deviation x 2 Thank you. Feb 26 '09 #7

 P: 47 All values in columns AJ, AK, AL & AM increased from first value and than decreased over time - as mean and standard deviation are changing. Thank you. Feb 26 '09 #8

 Expert Mod 15k+ P: 31,419 OK. Nearly there.Is it always true that [AJ]=0-[AL]? Is it possible for either [AJ] or [AL] to have a value of 0? If the answer to 2 is Yes then it's also possible that [AJ]=[AK]=[AL]=[AM]=0. PS. Until I hear back from you I'll look to preparing a formula based on the answers both being Yes. Feb 26 '09 #9

 Expert Mod 15k+ P: 31,419 Try this in AN100 : Expand|Select|Wrap|Line Numbers =\$D100*IF(\$U100<\$AK100,0,IF(\$U100<\$AJ100,1,IF(\$U100<\$AL100,2,IF(\$U100<\$AM100,3,4)))) This is designed to be dragable (down, or up, the column). Feb 26 '09 #10

 Expert Mod 15k+ P: 31,419 Forget that. Instead put in AO100 : Expand|Select|Wrap|Line Numbers =IF(\$U100<\$AK100,0,IF(\$U100<\$AJ100,1,IF(\$U100<\$AL100,2,IF(\$U100<\$AM100,3,4)))) Then put in AN100 simply : Expand|Select|Wrap|Line Numbers =\$D100*\$AO100 Feb 26 '09 #11

 P: 47 Hello NeoPa! Your help is truly appreciated - Thank you! [AH] - [AI] = [AJ] [AH] + [AI] = [AL] I don't know math that well, but I think that AJ & AL can be 0. I have data values in column U and [AH] is average value of U after every single event, while [AI] is Standard deviations of U after every single event. Feb 26 '09 #12

 Expert Mod 15k+ P: 31,419 @bcr123 I'm not sure about AN, but AO should certainly work with Conditional Formatting (from the Format menu). You may find a way to specify a condition for AN too. Have a look. Feb 26 '09 #13

 P: 47 Thank you. Will come back to confirm if it works or not. Thank you, once again! Feb 26 '09 #14

 P: 47 Thank you NeoPa. Works great. Will sit and work it properly tonight/tomorrow and will let you know is everything is ok Thank you! Feb 26 '09 #15

 Expert Mod 15k+ P: 31,419 You're welcome :) Good luck with your project. Feb 26 '09 #16

 P: 47 Thank you, once again. May I ask you here something else, that is not 100% related to formula issue that you have helped me with - Or proper way would be to post under new subject? Feb 26 '09 #17

 Expert Mod 15k+ P: 31,419 Post a separate thread if you would, but if you let me know in here that it's been posted (a link would be a bonus but I can find it anyway if not), then I'll check it out for you and respond. Feb 26 '09 #18

 P: 47 Feb 26 '09 #19

 P: 47 Hello NeoPa, Please let me know if there is way to get more than 3 conditional formatting that I am trying to use in my AO column. I need 4 or 5 colors, while conditional formatting is allowing me to use no more than 3. Thank you, as always, for your time and help! Feb 27 '09 #20

 P: 47 Also, is there a way to make, as an example, cell AN100 to have width of 2cm, while cell AN110 remains 3cm? Thank you! Feb 27 '09 #21

 P: 47 Sorry, there is one more question related to formulas that you have helped me with: If I want to drag formulas down AN column, but if I need them in every other row (AN 5, AN 7, etc.) - what is the best way to do it in order to avoid copy/pasting formulas in rows where I shouldn't have them (AN6, AN8, etc.). Thank you very much! Feb 27 '09 #22

 P: 47 Hello NeoPa - There is one more question. This is adjusted formula (it is in column AA now) to what you have made for me: =IF(\$L23<\$P23,0,IF(\$L23<\$Q23,1,IF(\$L23<\$R23,2,IF(\$ L23<\$S23,3,4)))) Please let me know what I need to to add the following: Same rules, same data, just one more condition is that same formula has to be confirmed in rows V, W, X, Y with M being what is L above and V, W, X & Y being what P, Q, R and S are in formula above. Thank you very much for your time! Feb 27 '09 #23

 P: 47 Just to clarify: Cell AA23 has to have value 0 if L23 is smaller than P23 and M23 is smaller than V23 and same rule applies for value 2, 3, 4 & 0 in AA23. Just to make sure I am clear with explanation - I somehow have to add AND to formula you made for me and make sure same conditions are met for value in M23. Thank you very much! Feb 27 '09 #24

 Expert Mod 15k+ P: 31,419 @bcr123 I don't think there is I'm afraid. You can define a catch-all, then three conditional formats, but that only gets you to 4. You could do it in the Change event in VBA of course. If that doesn't frighten you. Feb 27 '09 #25

 Expert Mod 15k+ P: 31,419 @bcr123 The only way I can think of having a similar effect at least, is to merge cells in some rows but not others. It means using AO for the data and shifting everything one to the right. Even this leaves subsequent columns as columns. This is also a very fiddly and complicated concept to implement. Feb 27 '09 #26

 Expert Mod 15k+ P: 31,419 @bcr123 Paste the formulas into the cells individually (You can select individually and paste into the selection.), or implement it in VBA. Working at a row or column level is convenient obviously, but it can only work if what you are doing relates to the whole row or column. Feb 27 '09 #27

 P: 47 Thank you NeoPa! Please let me know if can help me with my posts from today 0 10:16 & 10:18. Thank you for your 10:59, 11:23 & 11:30 posts – Will try to learn about VBA and until than will color required cells manually and will paste into every other row manually. Thank you very much for help me! Feb 27 '09 #28

 Expert Mod 15k+ P: 31,419 @bcr123 I think you may need to take another look at post #11. This formula is not an accurate representation of the logic in there, even assuming columns have moved. Feb 27 '09 #29

 Expert Mod 15k+ P: 31,419 @bcr123 I'm afraid I can't (other than my last post). I don't follow what you're asking as the terms used are too unclear. I suggest you try again to explain it, being very clear about what is referencing what. Feb 27 '09 #30

 P: 47 @NeoPa Hello and apologies for the confusion. Original formula was: IF(\$U100<\$AK100,0,IF(\$U100<\$AJ100,1,IF(\$U100<\$AL10 0,2,IF(\$U100<\$AM100,3,4)))) If U100 is smaller than AK100, than 0 is going as value in AA100, etc. I now have another problem that I don't know how to sort: If U100 is smaller than AK100 and and if M100 is smaller than W100 , than 0 is going as value in AA100. If U100 is smaller than AJ100 and if M100 is smaller than V100 than 1 is going as value in AA100. If U100 is smaller than AL100 and if M100 is smaller than X100 than 2 is going as value in AA100. If U100 is smaller than AM and if M100 is smaller than Y100 than 3 is going as value in AA100. If U100 is bigger than AM100 and if M100 is bigger than Y100 than 4 is going as value in AA100. Thank you for your time and I apologize for the confusion. Feb 27 '09 #31

 Expert Mod 15k+ P: 31,419 I don't know if this is clear to you, but you've left out great chunks of the logic here. What if (for instance) U100=W100? Feb 27 '09 #32

 P: 47 Can't attach PNG photo of portion of spreasheet. Feb 27 '09 #33

 P: 47 Could you please tell me how I can attach PNG form my PC - it is screen shot of my Excel and would like to post it and list all rules under it. Thank you! Feb 27 '09 #34

 Expert Mod 15k+ P: 31,419 In the reply or advanced edit screen, there is a button, Manage Attachments (you may need to scroll down), that enables you to add attachments. NB. There are some size limits to conform to. Feb 27 '09 #35

 P: 47 Here is a list of all rules. I've tried to attach PNG file, but no luck. Thank you for your time and help! All rules are IF rules: U100AM100 and if M100>Y100 then AA100 vlaue is 4 Always: AKW100) then AA100 value is 1 If U100V100) then AA100 value is 2 If U 100X100) then AA100 value is 2 If U 100Y100 then AA100 value is 2 If U100V100) then AA100 value is 2 If U 100X100) then AA100 value is 2 If U 100Y100 then AA100 value is 2 If U100>AL100 and if M>Y100 then AA100 value is 3 If U100>AL100 and if MV100) then AA100 value is 2 If U100>AL100 and if MW100) then AA100 value is 2 If U100>AL100 and if MAM100 and if MX100) then A100 value is 3 If U100>AM100 and if MV100) then A100 value is 2 If U100>AM100 and if MW100) then A100 value is 2 If U100>AM100 and if MU100>AJ100 AA100 value is 2 irrespective of M If X100>M100>V100 AA100 value is 2 irrespective of U Thank you! Feb 27 '09 #36

 Expert Mod 15k+ P: 31,419 This looks a little complicated to rush through now, but I notice there seems to be a lot more clear info here than in previous occasions so I'll look at it over the weekend and see what I can come up with. Feb 27 '09 #37

P: 47
Thank you very much NeoPa.

Attached is PNG file as snapshot of spreadsheet.

U from previous post may be substituted with L; AK with Q; AJ with P; AL with R and AM with S.

Whole idea is:

-When, both, U (L) and M are under -2 Standard deviations, value for AA is 0;
-When either U (L) or M is under -2 Stdev and another one is under -1 Stdev the value for AA is 1;
-When, both, U (L) and M are under -1 Standard deviations, value for AA is 1;
-When either U (L) or M is under -2 Stdev and another one is above -1 Stdev or above +1 Stdev or above +2 Stdev than AA is 2;
-When either U (L) or M is under -1 Stdev and another one is above -1 Stdev or above +1 Stdev or above +2 Stdev than AA is 2;

-When, both, U (L) and M are under +1 Stdev, but above -1 Stdev value for AA is 2;

-When, both, U (L) and M are above +2 Standard deviations, value for AA is 4;
-When, either U (L) or M is above +2 Stdev and another one is above +1 Stdev value for AA is 3;
-When, both, U (L) and M are above +1 Standard deviations, value for AA is 3;
-When, either U (L) or M is above +2 Stdev and another one is below +1 Stdev, -1 Stdev or -2; Stdev, then AA value is 2.
-When, either U (L) or M is above +1 Stdev and another one is below +1 Stdev, -1 Stdev or -2; Stdev, then AA value is 2.

Standard deviations related to column L are in blue (P-S) and Standard deviations related to column M are in green (V-Y).

Example, selected row :

AA value in that row should be 2 - L is smaller than R (+1 Stdev is 12.11) but bigger than P (-1 Stdev is 11.04), so despite M is larger than X (+1 Stdev is 993.25), AA remains 2, because only M is above +1 Stdev, while L remains below + 1 Stdev.

Thank you, once again, for your time.

Have a pleasant evening and weekend!
Attached Files
 1234.zip (19.0 KB, 58 views)
Feb 27 '09 #38

 Expert Mod 15k+ P: 31,419 Starting at line 2 (change if necessary) : Expand|Select|Wrap|Line Numbers =IF(AND(\$L2<\$Q2,\$M2<\$W2),0,IF(AND(\$L2<\$P2,\$M2<\$V2),1,IF(AND(\$L2<\$S2,\$M2<\$Y2),4,IF(AND(\$L2<\$R2,\$M2<\$X2),3,2)))) Mar 2 '09 #39

 Expert Mod 15k+ P: 31,419 It's necessarily more complicated, but I resolved you explanation down to the following to produce this : Expand|Select|Wrap|Line Numbers Step                    Value Order  Logic           Returned  A     U < -2 AND        M < -2             0  B     NOT Step A AND        U < -1 AND        M < -1             1  C     U >  2 AND        M >  2             4  D     NOT Step C AND        U >  1 AND        M >  1             3  E     Anything else      2 Mar 2 '09 #40

 P: 47 Thank you NeoPa - Your time and assistance are truly appreciated. Please note that there are several other possibilities: U <-2 AND M<-1 (but not <-2) should return 1 U <-1 (but not <-2) AND M<-1 should return 1 U >2 and M >1 (but not >2) should return 3 U >1 (but not >2) and M>2 should return 3 Thank you, once again! Mar 2 '09 #41

 Expert Mod 15k+ P: 31,419 I think you'll find (unless it's possible in your scenario for something to be less than -2 but not being less than -1) that these possibilities are indeed covered by steps B & D respectively. If you can find any situation where it doesn't work for you, please post the details and I'll look at it again for you. Mar 2 '09 #42

P: 47
Hello NeoPa,

Please review attached files when you have a moment:

File 1.png –
In highlighted line AA should have value 3, as L26>R26 (but L26<S26)
AND
M26>X26 (but M26<Y26)
--------------------------
File 2.png –

AA should have value 4, as L48>S48
AND
M48>Y48
--------------------------
File 3.png –

AA should have value 3, as L86>R86 (but L86<S86)
AND
M86>X86

Thank you very much – you are great contributor to this forum and I am grateful for your time and help.

(Please note that I have reorganized Excel, so these 2 calculations are next to each other, so I can post it nicely. I just have replaced U with L in your original formula and I’ve adjusted row number as well.)
Attached Files
 NeoPa.zip (93.3 KB, 51 views)
Mar 2 '09 #43

 Expert Mod 15k+ P: 31,419 I changed the earlier one to use \$L instead of \$U, but that wasn't the problem. I forgot to use > instead of < for the 4 & 3 results. Try this new fixed version : Expand|Select|Wrap|Line Numbers =IF(AND(\$L2<\$Q2,\$M2<\$W2),0,IF(AND(\$L2<\$P2,\$M2<\$V2),1,IF(AND(\$L2>\$S2,\$M2>\$Y2),4,IF(AND(\$L2>\$R2,\$M2>\$X2),3,2)))) Mar 3 '09 #44

 Expert Mod 15k+ P: 31,419 This question's been bugging me for a while now, and looking at your data nicely laid out like that it's even more of a curiosity for me now : Why do you list STDV (-1) before STDV (-2)? Mar 3 '09 #45

 P: 47 Thank you NeoPa! Your help is appreciated. I've copied formula into Excel and will post later in the evening or tomorrow if it is working ok - I believe it will and I am grateful for your time and all the help. Thank you! Mar 3 '09 #46

 P: 47 @NeoPa Very strange for me as well. I've noticed it and it's been lying there and staring at me. I'm confused about it as well and will re-organize Excel again once all is done with formulas, etc. :) Thank you, once again! Mar 3 '09 #47

 Expert Mod 15k+ P: 31,419 @bcr123 I have to say it's been a pleasure working with someone as well mannered as you. Mar 3 '09 #48

 P: 47 To reciprocate with the compliment – It is very unusual for newbie to find so much help and patience from senior forum member(s) on any forum and I am truly grateful for your time, patience and help. So far, my experience with bytes.com has been very positive and in few short days I’ve learned a lot and I will keep on visiting these pages in order to read and learn. Will let you know later in the evening or tomorrow how formula is performing. Thank you NeoPa! Mar 3 '09 #49

 P: 47 Thank you NeoPa! Formula is working great! Thank you very much for your time. Mar 4 '09 #50

50 Replies