Math Is Fun Forum
  Discussion about math, puzzles, games and fun.   Useful symbols: ÷ × ½ √ ∞ ≠ ≤ ≥ ≈ ⇒ ± ∈ Δ θ ∴ ∑ ∫ • π ƒ -¹ ² ³ °

You are not logged in.

#1 2019-07-21 22:59:37

Wood
Member
Registered: 2019-07-19
Posts: 8

excel formula

i need excel help.a1:a6 base values of b1:b6.In c1:c3 search values500,400,300.a1 is 300,b1 is 285,a2 is 400,b2 is 350,a3 is 300,b3 is 290,a4 is 500,b4 is 467,a5 is 300,b5 is 285,a6 is 400,b6 is 320.I need display in d1 is 467,like d2 is 350,320.in same cell,d3 ans 290,285.That is number value one time in single cell with commos with descending order.kindly help me sir.

Offline

#2 2019-07-22 20:08:45

Bob
Administrator
Registered: 2010-06-20
Posts: 8,914

Re: excel formula

hi Wood,

It looks like you want the 'd' column to have the 'b' values but in descending order of size, and grouped so that all the numbers in the d3 cell, for example, are in the 200-300 range in descending order across the cell, with commas between.

That's quite a challenge as Excel only allows one number per cell.  You can convert numbers to text with the command =TEXT(1234,"##0").  The function needs two inputs; the first is the value as a number; the second is the format you want.  I've assumed that all your data are whole numbers,

Then you'd have to add text together with the command =CONCATENATE(text1, " , ", [text2], ...). Note that the insert " , " puts in the commas for you.

First use the SORT function to re-order the B column.  I cannot test this step because the function doesn't work in my version of Excel.

Then you've got to find the values in each range.  I think the simplest way to do this would be with =IF. 

eg =IF(AND(B1>200,B1<300),B1,)  This will pick out all the numbers in that range and place them in that column.  You'll have to do this for each range.

So you'd need a lot of columns just to separate out the ranges, then convert the results to text, then concatenate the text with "," between to insert the commas.

Wouldn't you rather use some computer code for this smile

Bob


Children are not defined by school ...........The Fonz
You cannot teach a man anything;  you can only help him find it within himself..........Galileo Galilei
Sometimes I deliberately make mistakes, just to test you!  …………….Bob smile

Offline

#3 2019-07-23 05:15:02

Wood
Member
Registered: 2019-07-19
Posts: 8

Re: excel formula

Sir, first of thank you for your  nice help. I simply explain a1 is 2, a2 is 7,a3 is 1, a4 is 3,a5 is 1.b1 is search value is 5 less or equal to, then return all numbers, descending order, in one cell d1 itself single comma separated cell . Answer in d1 3,2,1.

Offline

#4 2019-07-23 05:17:52

Wood
Member
Registered: 2019-07-19
Posts: 8

Re: excel formula

Array formula need in excel

Offline

#5 2019-07-23 18:56:59

Bob
Administrator
Registered: 2010-06-20
Posts: 8,914

Re: excel formula

hi Wood

I've looked up array formulas in Excel help, and I don't think this will do what you want.

Please would you check if you can sort a column into order (descending). 

eg if B1 = 3; B2 = 5; B3 = 1; B4 = 7; B5 = 2.

Try the formula SORT.  Can you get:

C1 = 7; C2 = 5; C3 = 3; C4 = 2; C5 = 1

My version of Excel is too old and doesn't have this formula.

Bob


Children are not defined by school ...........The Fonz
You cannot teach a man anything;  you can only help him find it within himself..........Galileo Galilei
Sometimes I deliberately make mistakes, just to test you!  …………….Bob smile

Offline

#6 2019-07-23 23:36:43

Wood
Member
Registered: 2019-07-19
Posts: 8

Re: excel formula

Need less or equal to 5.answer must be single cell in d1 itself 5,3,2,1.

Offline

#7 2019-07-23 23:38:33

Wood
Member
Registered: 2019-07-19
Posts: 8

Re: excel formula

All answers in single comma separated cell in d1 with descending order, without repeat numbers in excel. What formula need that

Offline

#8 2019-07-24 03:03:04

Bob
Administrator
Registered: 2010-06-20
Posts: 8,914

Re: excel formula

Yes I understand that.  I do not think there is one formula which will do this.  So we must do it in steps.

Step 1.  Sort the B column into order.  Are you able to do that?

Then this:

aFbFF5Q.gif

Bob


Children are not defined by school ...........The Fonz
You cannot teach a man anything;  you can only help him find it within himself..........Galileo Galilei
Sometimes I deliberately make mistakes, just to test you!  …………….Bob smile

Offline

#9 2019-07-24 10:38:31

phrontister
Real Member
From: The Land of Tomorrow
Registered: 2009-07-12
Posts: 4,653

Re: excel formula

Hi Bob & Wood,

Here's an image of my attempt, with formulas displayed in the lower section. I used some different values in column A than in your posts, just for testing purposes.

B1 contains the search value (5, in this case), which with my spreadsheet meant using an extra column (E). However, it enables changing the search value (if required) easily.

Column C returns all values <= the value in B1 ('5').

Column D gives the list of all unique Column C values (excluding blanks), in descending order.

The single cell E1 contains the comma-delimited output (in descending order) of the range D1:D4. Unfortunately I couldn't think of an automatic way to use TEXTJOIN that doesn't require having to select the cell range.

IutDBjV.png

============================================================================================

Hi Bob,

With my version of Excel, 'CONCATENATE' joins numbers and returns the result as a text string, so there's no need for column C's conversion to text.

You may already know this: CONCATENATE has an alternative 'ampersand operator' option, used like so:
     =C1&","&C2&","&C3&","&C4&","&C5


"The good news about computers is that they do what you tell them to do. The bad news is that they do what you tell them to do." - Ted Nelson

Offline

#10 2019-07-24 18:02:04

Wood
Member
Registered: 2019-07-19
Posts: 8

Re: excel formula

Phrontister Sir, 
Thank u. Without concatenate, textjoin, only use array formula index match large to get answers in one cell. I need help... First post.

Offline

#11 2019-07-24 18:09:29

phrontister
Real Member
From: The Land of Tomorrow
Registered: 2009-07-12
Posts: 4,653

Re: excel formula

Hi Wood,

Could you please show us the best formulas you have tried.

Thanks.


"The good news about computers is that they do what you tell them to do. The bad news is that they do what you tell them to do." - Ted Nelson

Offline

#12 2019-07-24 18:09:42

Wood
Member
Registered: 2019-07-19
Posts: 8

Re: excel formula

Phrontister,
Kindly look my first post. Index, match large used without cse first match come. Then we press cse,first match number it's shows all numbers in single comma separated in one cell without helper column.

Offline

#13 2019-07-24 18:44:42

phrontister
Real Member
From: The Land of Tomorrow
Registered: 2009-07-12
Posts: 4,653

Re: excel formula

I'm sorry, Wood, but I know nearly nothing about arrays and {INDEX,MATCH,LARGE} formulas, and unfortunately I don't have enough time to research and learn this area.

I hope someone else here can help you (and maybe Bob knows how), but if not it may be best for you to try an Excel forum.


"The good news about computers is that they do what you tell them to do. The bad news is that they do what you tell them to do." - Ted Nelson

Offline

#14 2019-07-24 22:53:51

Wood
Member
Registered: 2019-07-19
Posts: 8

Re: excel formula

Bob, any idea sir

Offline

#15 2019-07-25 03:21:10

Bob
Administrator
Registered: 2010-06-20
Posts: 8,914

Re: excel formula

hi

I've not met the function LARGE before.  That was useful as it gives me a way to sort the B column.  I do not understand how INDEX and MATCH will help. 

You cannot get more than one number into cell D1  so the only way I can think to do this is to convert the numbers into text and then join the text together.  Here is my sheet with the actual cell contents at the top, and then the formulas I used below.  I have annotated it so you can follow what I did.

HNRyOb3.gif

Bob


Children are not defined by school ...........The Fonz
You cannot teach a man anything;  you can only help him find it within himself..........Galileo Galilei
Sometimes I deliberately make mistakes, just to test you!  …………….Bob smile

Offline

#16 2019-08-14 09:08:35

phrontister
Real Member
From: The Land of Tomorrow
Registered: 2009-07-12
Posts: 4,653

Re: excel formula

Hi;

I spent a bit of time trying find a solution that used the OP's requirements, but I failed and went back to what I had tried before with TEXTJOIN because I felt I'd got close then (sorry, Wood).

This time I think I succeeded (see image). The method returns column D results that are distinct, comma-delimited, and with each cell's contents sorted into descending order...as per post #1's requirements.

Some comments:
   1. I used Excel's 'Sort Z to A' feature to sort each column D cell into descending order (don't know how to do it via formula).
   2. I haven't met post #10's requirements (don't know how).
   3. I populated column C via LARGE, with distinct values in descending order.

MngPMiM.jpg

Last edited by phrontister (2019-08-25 11:57:01)


"The good news about computers is that they do what you tell them to do. The bad news is that they do what you tell them to do." - Ted Nelson

Offline

Board footer

Powered by FluxBB