Excel Formula

[bcr123]

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!

[NeoPa]

Is there any known order of the various values?
EG. AJ always greater than AK.

[bcr123]

Hello NeoPa.

AJ is always greater than AK.
AL is always smaller than AM.

Thank you!

[NeoPa]

Can AM ever be smaller than AK?
Can AL ever be smaller than AJ?

[bcr123]

Hello.
Thank you very much.

AM can't be smaller than AK.
AL can't be smaller than AJ.

[NeoPa]

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).

[bcr123]

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.

[bcr123]

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.

[NeoPa]

OK. Nearly there.

1. Is it always true that [AJ]=0-[AL]?
2. 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.

[NeoPa]

Try this in AN100 :

=$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).

[NeoPa]

Forget that. Instead put in AO100 :

=IF($U100<$AK100,0,IF($U100<$AJ100,1,IF($U100<$AL100,2,IF($U100<$AM100,3,4))))

Then put in AN100 simply :

=$D100*$AO100

[bcr123]

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.

[NeoPa]

@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.

[bcr123]

Thank you.
Will come back to confirm if it works or not.

Thank you, once again!

[bcr123]

Thank you NeoPa.

Works great.

Will sit and work it properly tonight/tomorrow and will let you know is everything is ok

Thank you!

[NeoPa]

You're welcome :)

Good luck with your project.

[bcr123]

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?

[NeoPa]

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.

[bcr123]

http://bytes.com/topic/access/answer...ie#post3467717

Thank you!

[bcr123]

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!

[bcr123]

Also, is there a way to make, as an example, cell AN100 to have width of 2cm, while cell AN110 remains 3cm?

Thank you!

[bcr123]

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!

[bcr123]

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!

[bcr123]

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!

[NeoPa]

@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.

[NeoPa]

@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.

[NeoPa]

@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.

[bcr123]

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!

[NeoPa]

@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.

[NeoPa]

@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.

[bcr123]

@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.

[NeoPa]

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<AK100, but M100>=W100?

[bcr123]

Can't attach PNG photo of portion of spreasheet.

[bcr123]

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!

[NeoPa]

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.

[bcr123]

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:

U100<AK100 and if M100<W100, then AA100 vlaue is 0

U100<AJ100 and if M100<V100, then AA100 vlaue is 1

U100<AL100 and if M100<X100, then AA100 vlaue is 2

U100<AM100 and if M100<Y100, then AA100 vlaue is 3

U100>AM100 and if M100>Y100 then AA100 vlaue is 4

Always:
AK<AJ<AL<AM
W<V<X<Y
U is related to AK, AJ, AL, AM
M is related to W, V, X, Y

If U100<AK100 and if M100<V100 (but M100>W100) then AA100 value is 1
If U100<AK100 and if M<X100 (but M100>V100) then AA100 value is 2
If U 100<AK100 and if M<Y100 (but M100>X100) then AA100 value is 2
If U 100<AK100 and if M>Y100 then AA100 value is 2

If U100<AJ100 and if M<W100 then AA100 value is 1
If U100<AJ100 and if M<X100 (but M100>V100) then AA100 value is 2
If U 100<AJ100 and if M<Y100 (but M100>X100) then AA100 value is 2
If U 100<AJ100 and if M>Y100 then AA100 value is 2

If U100>AL100 and if M>Y100 then AA100 value is 3
If U100>AL100 and if M<X100 (but M100>V100) then AA100 value is 2
If U100>AL100 and if M<V100 (but M100>W100) then AA100 value is 2
If U100>AL100 and if M<W100 then AA100 value is 2

If U100>AM100 and if M<Y100 (but M100>X100) then A100 value is 3
If U100>AM100 and if M<X100 (but M100>V100) then A100 value is 2
If U100>AM100 and if M<V100 (but M100>W100) then A100 value is 2
If U100>AM100 and if M<W100 then A100 value is 2

If AL100>U100>AJ100 AA100 value is 2 irrespective of M
If X100>M100>V100 AA100 value is 2 irrespective of U

Thank you!

[NeoPa]

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.

[bcr123]

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!

[NeoPa]

Starting at line 2 (change if necessary) :

=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))))

[NeoPa]

It's necessarily more complicated, but I resolved you explanation down to the following to produce this :

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

[bcr123]

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!

[NeoPa]

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.

[bcr123]

Hello NeoPa,

Thank you for your time.

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.)

[NeoPa]

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 :

=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))))

[NeoPa]

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)?

[bcr123]

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!

[bcr123]

@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!

[NeoPa]

@bcr123
I have to say it's been a pleasure working with someone as well mannered as you.

[bcr123]

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!

[bcr123]

Thank you NeoPa!

Formula is working great!

Thank you very much for your time.