using two functions in the same cell

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Brian204845 - 04 Feb 2008 04:13 GMT

OK here's the problem. Asignment in school due tomorrow. I have to use the
MIN function to find the lowest expense in a row of three cells (B38 C38 and
D38). No problem thee =MIN(B38:D38). But I have to embed that function
within the LOOKUP function in order to find the matching column label (Year 1
Year 2 and Year 3) which are in cells B32 C32 and D32. I have to print
"minimum expense year" under the the appropriate cell with the lowest
expense. The cells immediately below the three cells with the expense
numbers are B39 C39 and D39. I can't seem to get this right. Any
suggestions would be appreciated! Brian

Reply to this Message

T. Valko - 04 Feb 2008 04:59 GMT

Not real sure what you're wanting but here's one way to get the column
header:

Year1...Year2...Year3
..50.........27........99

="Year"&MATCH(MIN(B38:D38),B38:D38,0)

If your column headers aren't really named "Year_n":

=INDEX(B32:D32,MATCH(MIN(B38:D38),B38:D38,0))

>I have to embed that function within the LOOKUP function

The LOOKUP function *requires* the lookup_vector be sorted in ascending
order. If it's not sorted it won't work properly.

Signature

Biff
Microsoft Excel MVP

> OK here's the problem. Asignment in school due tomorrow. I have to use
> the
[quoted text clipped - 8 lines]
> numbers are B39 C39 and D39. I can't seem to get this right. Any
> suggestions would be appreciated! Brian

Reply to this Message

Brian204845 - 04 Feb 2008 05:17 GMT

Thanks Biff. The assignment question is this, "Find the lowest total exoense
amount and print the message 'minimum expense year'under the cell where the
lowest total expense occurs." I can use the MIN function to find the lowest
value easily enough but the problem is then to get the formula to print that
message. Perhaps I'm looking too hard using the LOOKUP function. Can that
minimum function be embedded in another function to print that message in the
cell below the lowest total. The colums are indeed titled Year 1 through 3
left to right.

> OK here's the problem. Asignment in school due tomorrow. I have to use the
> MIN function to find the lowest expense in a row of three cells (B38 C38 and
[quoted text clipped - 5 lines]
> numbers are B39 C39 and D39. I can't seem to get this right. Any
> suggestions would be appreciated! Brian

Reply to this Message

Brian204845 - 04 Feb 2008 05:21 GMT

Here's what I came up with but the syntax ia wrong.
=LOOKUP(MIN(B38:D38),B32:D32,B39:D39)

Reply to this Message

T. Valko - 04 Feb 2008 05:59 GMT

Try this in B39 and copy across to D39:

=IF(B38=MIN($B38:$D38),"minimum expense year","")

I don't know how "invovled" your assignment is but some things to consider:

If *all* the cells in B338:D38 are empty the formula will return the message
in each cell B39:D39

If there are multiple instances of the MIN, each instance will return the
message.

Signature

Biff
Microsoft Excel MVP

> Here's what I came up with but the syntax ia wrong.
> =LOOKUP(MIN(B38:D38),B32:D32,B39:D39)
[quoted text clipped - 11 lines]
>> numbers are B39 C39 and D39. I can't seem to get this right. Any
>> suggestions would be appreciated! Brian

Reply to this Message

Brian204845 - 04 Feb 2008 06:17 GMT

Thanks Biff! That worked. I now see how it works!

> Try this in B39 and copy across to D39:
>
[quoted text clipped - 23 lines]
> >> numbers are B39 C39 and D39. I can't seem to get this right. Any
> >> suggestions would be appreciated! Brian

Reply to this Message

T. Valko - 04 Feb 2008 07:10 GMT

You're welcome. Thanks for the feedback!

Signature

Biff
Microsoft Excel MVP

> Thanks Biff! That worked. I now see how it works!
>
[quoted text clipped - 31 lines]
>> >> numbers are B39 C39 and D39. I can't seem to get this right. Any
>> >> suggestions would be appreciated! Brian

Reply to this Message