How do I return the lowest value of three other fields?

D

David K

My form's users enter three numbers (between 1 and 5) in Columns A, B, and C.
If all three numbers are (for example) "4" then the table's "Column D" must
contain the number 4. But if the numbers differ, then the lowest one goes in
column D.

I've been using Expression Builder and the IIF function. It gets me most of
the way, but nothing I do with the three numbers (averaging, square root of
sum of squares...) returns a value equal to the lowest of the three numbers.
In Excel, I'd use the "Truncate" function on the mean value (but even then,
I'd have an error if the three numbers differ by more than a value of "1").
 
D

David K

Almost...

If A, B, and C (respectively) are 5,5,4
Then the value returned is 5 (but should be 4).
This happens because the first condition isn't met (A=B, it's not >) so the
default value (A) is entered.

I tried this with <, and I get the same problem with a different combination
of A,B and C. I guess I can use a greater number of nested statements to
cover the condition where A=B, but I was hoping for a simpler solution.

KARL DEWEY said:
The answer is Nested IIFs. Try this ---
IIF([A]>, IIF(>[C], [C], ), [A])

--
KARL DEWEY
Build a little - Test a little


David K said:
My form's users enter three numbers (between 1 and 5) in Columns A, B, and C.
If all three numbers are (for example) "4" then the table's "Column D" must
contain the number 4. But if the numbers differ, then the lowest one goes in
column D.

I've been using Expression Builder and the IIF function. It gets me most of
the way, but nothing I do with the three numbers (averaging, square root of
sum of squares...) returns a value equal to the lowest of the three numbers.
In Excel, I'd use the "Truncate" function on the mean value (but even then,
I'd have an error if the three numbers differ by more than a value of "1").
Click to expand...
Click to expand...
 
D

Douglas J. Steele

Try

IIF([A]>, IIF(>[C], [C], ), IIf([A] > [C], [C], [A]))


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


David K said:
Almost...

If A, B, and C (respectively) are 5,5,4
Then the value returned is 5 (but should be 4).
This happens because the first condition isn't met (A=B, it's not >) so
the
default value (A) is entered.

I tried this with <, and I get the same problem with a different
combination
of A,B and C. I guess I can use a greater number of nested statements to
cover the condition where A=B, but I was hoping for a simpler solution.

KARL DEWEY said:
The answer is Nested IIFs. Try this ---
IIF([A]>, IIF(>[C], [C], ), [A])

--
KARL DEWEY
Build a little - Test a little


David K said:
My form's users enter three numbers (between 1 and 5) in Columns A, B,
and C.
If all three numbers are (for example) "4" then the table's "Column D"
must
contain the number 4. But if the numbers differ, then the lowest one
goes in
column D.

I've been using Expression Builder and the IIF function. It gets me
most of
the way, but nothing I do with the three numbers (averaging, square
root of
sum of squares...) returns a value equal to the lowest of the three
numbers.
In Excel, I'd use the "Truncate" function on the mean value (but even
then,
I'd have an error if the three numbers differ by more than a value of
"1").
Click to expand...
Click to expand...
Click to expand...
 
N

Nicholas Scarpinato

By the way, in Excel, you could just use the Min function to pull the lowest
number... there's no need to truncate the average or anything like that. Just
use something like =Min(a1:a3) and you're done. Just figured I'd share that
info, in case you ever need it for later. But Douglas' formula seems to be
the solution you need for Access, I tried it and it worked for me.

Douglas J. Steele said:
Try

IIF([A]>, IIF(>[C], [C], ), IIf([A] > [C], [C], [A]))


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


David K said:
Almost...

If A, B, and C (respectively) are 5,5,4
Then the value returned is 5 (but should be 4).
This happens because the first condition isn't met (A=B, it's not >) so
the
default value (A) is entered.

I tried this with <, and I get the same problem with a different
combination
of A,B and C. I guess I can use a greater number of nested statements to
cover the condition where A=B, but I was hoping for a simpler solution.

KARL DEWEY said:
The answer is Nested IIFs. Try this ---
IIF([A]>, IIF(>[C], [C], ), [A])

--
KARL DEWEY
Build a little - Test a little


:

My form's users enter three numbers (between 1 and 5) in Columns A, B,
and C.
If all three numbers are (for example) "4" then the table's "Column D"
must
contain the number 4. But if the numbers differ, then the lowest one
goes in
column D.

I've been using Expression Builder and the IIF function. It gets me
most of
the way, but nothing I do with the three numbers (averaging, square
root of
sum of squares...) returns a value equal to the lowest of the three
numbers.
In Excel, I'd use the "Truncate" function on the mean value (but even
then,
I'd have an error if the three numbers differ by more than a value of
"1").
Click to expand...
Click to expand...
Click to expand...
 
K

KARL DEWEY

I did not try but this might work --
IIF([A]>=, IIF(>=[C], [C], ), [A])

--
KARL DEWEY
Build a little - Test a little


David K said:
Almost...

If A, B, and C (respectively) are 5,5,4
Then the value returned is 5 (but should be 4).
This happens because the first condition isn't met (A=B, it's not >) so the
default value (A) is entered.

I tried this with <, and I get the same problem with a different combination
of A,B and C. I guess I can use a greater number of nested statements to
cover the condition where A=B, but I was hoping for a simpler solution.

KARL DEWEY said:
The answer is Nested IIFs. Try this ---
IIF([A]>, IIF(>[C], [C], ), [A])

--
KARL DEWEY
Build a little - Test a little


David K said:
My form's users enter three numbers (between 1 and 5) in Columns A, B, and C.
If all three numbers are (for example) "4" then the table's "Column D" must
contain the number 4. But if the numbers differ, then the lowest one goes in
column D.

I've been using Expression Builder and the IIF function. It gets me most of
the way, but nothing I do with the three numbers (averaging, square root of
sum of squares...) returns a value equal to the lowest of the three numbers.
In Excel, I'd use the "Truncate" function on the mean value (but even then,
I'd have an error if the three numbers differ by more than a value of "1").
Click to expand...
Click to expand...
Click to expand...
 
D

Douglas J. Steele

Karl: You need to have a third comparison in there, just as I did. Just
because A is less than B, you have no idea what its value is with respect to
C.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


KARL DEWEY said:
I did not try but this might work --
IIF([A]>=, IIF(>=[C], [C], ), [A])

--
KARL DEWEY
Build a little - Test a little


David K said:
Almost...

If A, B, and C (respectively) are 5,5,4
Then the value returned is 5 (but should be 4).
This happens because the first condition isn't met (A=B, it's not >) so
the
default value (A) is entered.

I tried this with <, and I get the same problem with a different
combination
of A,B and C. I guess I can use a greater number of nested statements to
cover the condition where A=B, but I was hoping for a simpler solution.

KARL DEWEY said:
The answer is Nested IIFs. Try this ---
IIF([A]>, IIF(>[C], [C], ), [A])

--
KARL DEWEY
Build a little - Test a little


:

My form's users enter three numbers (between 1 and 5) in Columns A,
B, and C.
If all three numbers are (for example) "4" then the table's "Column
D" must
contain the number 4. But if the numbers differ, then the lowest one
goes in
column D.

I've been using Expression Builder and the IIF function. It gets me
most of
the way, but nothing I do with the three numbers (averaging, square
root of
sum of squares...) returns a value equal to the lowest of the three
numbers.
In Excel, I'd use the "Truncate" function on the mean value (but even
then,
I'd have an error if the three numbers differ by more than a value of
"1").
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Convert code to lookup three criteria return fourth 15
return multiple values when within range (greater than and less than) 11
Function select second lowest 4
Returning a value if multiple conditions are met 2
LOOKUP VALUE OCCURS >ONCE IN TABLE ARRAY - RETURN LAST OCCURRENCE 6
SUMIF using date ranges & referencing account numbers 1
find lowest number and return it's adjacent cell 7
How may I lookup and return a value between two numbers 2

Top