Complicated formula?

Sam Summers

Registered User.
Local time
Today, 09:40
Joined
Sep 17, 2001
Messages
939
Hi guys,

searched and attempted but not getting this one at all:confused:

I need a formula based from one cell AE30 answer to be displayed in AF30:

If AE30 value >2 then AF30 = 0.2
If AE30 value is between 1 and 2 then AF30 =0.1
Else AF30 = 0

Any ideas?

Many thanks in advance
 
Hi there,
Thanks very much for your help.
It didn't totally work but is pointing me in the right direction?

I have this now:

=IF(AE31=0,"0.2",IF(AE31>0<6,"0.1","0"))

to try and achieve this:

0 = 0.2
>0 but <6 = 0.1
>5 = 0
 
what didn't work about my formula?
I'm looking at the two descriptions you've provided, and they don't look the same.

If AE30 value >2 then AF30 = 0.2
If AE30 value is between 1 and 2 then AF30 =0.1
Else AF30 = 0

0 = 0.2
>0 but <6 = 0.1
>5 = 0

I'll try again with your new requirements
=if(AE30=0,0.2,if(AE30<=6,0.1,0))
I'm assuming you're putting integers into the cells, since your description would make 5.5 be both 0.1 and 0.
 
Last edited:
Thank you so much for your patience.

I will try and explain what the request is -

Basically I have two columns AE and AF

In AE personnel will enter the statistics

and the result will be shown in AF (which is where our formula is)

If they enter 0 then the result needs to be 0.2
If they enter a number between 1 and 5 then the result = 0.1
If they enter a number >5 then the result = 0


Sorry if I didn't explain it properly before.

Many many thanks.

I just don't seem to have grasped the concept of the formula at the moment.

Before with your formula I just had to put inverted commas around the result numbers - i.e "0,2"
 
I feel sorry for James as you keep changing the requirements.
Note that putting the number in quotes "0.2" means that the number will be stored as text.
The formula is resolve from left to right, therefore a test can ignore previous tests as they will have been met.
Assuming only integers are entered in col AE then test for 0 then for >5 and the rest have to be 1 2 3 4 or 5

Brian
 
Here's how an If works.
If(booleanTest,True,False)
you put something into the booleanTest which can either be true of false.
So with my last formula, =if(AE30=0,0.2,if(AE30<=6,0.1,0)) I am testing AE30 to see if it equals 0, if so I put 0.2 into whichever cell my formula is placed in (AF30). If AE30 is not equal to 0, then the false part of the if formula happens, but I put a whole new IF in the false part. This second If says that if AE30 is less than or equal to 6 (should be 5 now that I'm looking at your requirements again), then the true part of the If happens which is putting 0.1 into the cell with the formula. If this second If is not true (then AE30 >5) then put 0 into the cell.
 
I haven't changed anything Brian I have just tried to clarify things to help find the answer.
I don't anything you explained Brian as I am not at your level?

Thanks for your help James.
I will try that and let you know how I get on?

Thanks again
 
Well now Sam you have changed some of the values, as noted by James, but let's not let that get in the way of your education.
You did understand the issue of the quotes?

James, I would test in the order I stated as then non integer values are catered for

=If(AE30=0,0.2,If(AE30>5,0,0.1))

Ie anything>0 and<=5 gives0.1


Brian
 
you can also use switch

AF = Switch(AE = 0, 0.2, AE <= 5, 0.1, AE > 5, 0)
 
I don't think that switch exists as a Worksheet function, but I could be out of date.

Brian
 
sorry for that i thought we are in msA.
 
Don't worry it's happened before,, after all this an Access forum.

Brian
 
Brian,
I usually go from smallest up, or largest down in my tests like this so that if an additional test ever needs to be made it will be easier to modify the if's.
I'm not following what you meant by, "I would test in the order I stated as then non integer values are catered for"
I don't see a difference between
=If(AE30=0,0.2,If(AE30>5,0,0.1))
or
=if(AE30=0,0.2,if(AE30<=5,0.1,0))
but if Sam decides in the future of an additional test, it would be easier to do something like the following
=if(AE30=0,0.2,if(AE30<=3,0.1,if(AE30<=5,0.05,0)))
 
I think I must have been having a senior moment when I wrote the sentence you quoted.

I cannot argue with your logic but I don't try to second guess where the program may need to change.

Brian
 
LOL - was following the thread and got lost.
It shows that asking a good question can often be harder than providing a good answer.
I certainly resemble that remark myself.
 
Hi guys,
Thanks for all your ongoing help.
I'm just back to work now.
Tried all the options but still no joy?

So my last attempt is to post exactly what they are requesting:

"
  • S1=0.3 if there is no recordable accident/incident in the month for CONTRACTOR and 0 otherwise.
  • S2=0.3 if there is no recordable HSE anomaly in the month for CONTRACTOR, 0.15 if there are recordable anomalies between 1 and 8 and 0 otherwise.
  • S3=0.2 if numbers of HSE visit or audit are more than 2, 0.1 if number of HSE visit or audit are between 1 and 2 and 0 otherwise in the month.
  • S4=0.2 if no environmental remarks in the month attributable to CONTRACTOR’s activities, 0.1 if number of remarks are between 1 and 5 and 0 otherwise.
"

S1 cell No. is AE28
S2 cell No. is AE29
S3 cell No. is AE30
S4 cell No. is AE31

I keep trying to get my head round it but still confused?:confused:
 
Welcome back Sam, but you sure know how to confuse an old guy.

S3and s4 look like things that have been discussed , but both referred to AE30 and AF30.

Could you post a small worksheet showing examples of data and expected results, post results in red.

Brian
 
Hi Brian and many thanks again for your patience and continued help with this.

I have included a screen shot which may help as the sheet is too big as it also contains other sections.

I'll try and clarify though:

the value input column for S1 to S4 is AE28 to AE31 respectively and the column that contains the formula and displays the result for the corresponding S1 to S4 cells is AF28 to AF31 respectively.

Does this help at all?
 

Attachments

  • Screen Shot 2015-09-28 at 11.03.30.png
    Screen Shot 2015-09-28 at 11.03.30.png
    33 KB · Views: 176
I'm always amazed at the modern approach to testing, just throw live data at it.
I would design a quick worksheet with ALL the options in it, but only the relevant data, two columns I think, and thus be able to know easily thatmy formula works .

I'll give it a look but it will have to be later today.

Brian
 

Users who are viewing this thread

Back
Top Bottom