Creating an dynamic array from 3 parameters

[philou54520]

Hi all,

I am really a beginner and a little bit ashamed because I could not find after extensive search how to generate a simple array (One dimension) but depending on 3 parameters. I would like to create a serial dilution array where the inputs are: Concentration of stock solution, dilutional factor and number of dilutions. Easy right? I can't manage to do this.

For example:
Stock solution concentration: 5000
Dilutional factor: 2
Number of dilutions: 7

Should generate an array like this:
{5000, 2500, 1250, 625, 312.5, 156.25, 78.125, 39.0625}

Thanks for your help!

Phil

 

[Peter_SSs]

Basically it is just for generating a template for ELISA tests (which are used in biology). I would like the members of my lab to use the same EXCEL templates they just have to enter the stock solution and not doing inefficient repetitive tasks

That doesn't really answer my question.
What is the layout of your worksheet like?
What is the underlying data?
Where should this generated 'array' go or how should it be used?

Could you show us a small sample (dummy data) of what your worksheet looks like & include the expected results? My signature block below has a link with suggestions for how to do that so we can copy/paste the sample data to test with.

 

[philou54520]

That doesn't really answer my question.
What is the layout of your worksheet like?
What is the underlying data?
Where should this generated 'array' go or how should it be used?

Could you show us a small sample (dummy data) of what your worksheet looks like & include the expected results? My signature block below has a link with suggestions for how to do that so we can copy/paste the sample data to test with.

I can't manage to upload a screen shot. It doesn't work with dropbox. As I said before, it is a very basic function I just want the people to put their initial concentration and it will do math for them. that's it

 

[Peter_SSs]

.. it is a very basic function ..

It may be, but we, who know nothing about what your worksheet looks like or how you want the results displayed, need to understand it before we can help.
Perhaps we are being confused by the word "array", which can be interpreted more than one way with Excel. Do you just want the results displayed individually in a number of separate cells?

It doesn't work with dropbox.

What doesn't work with dropbox?

 

[philou54520]

Do you just want the results displayed individually in a number of separate cells?

YES I do. maybe the term Array is not a good one? It is just making repeated division in a number of define rows. I thought an array was the best method to do that.

What doesn't work with dropbox?

I can't share the image link with the dropbox link

 

[Peter_SSs]

YES I do.

In that case, would this suffice?
Formula in B4, copied down

Excel Workbook
	A	B
1	Stock solution concentration	5000
2	Dilutional factor	2
3	Number of dilutions	7
4		5000
5		2500
6		1250
7		625
8		312.5
9		156.25
10		78.125
11		39.0625
12
Dilution

I can't share the image link with the dropbox link

What I was suggesting, like the screen shot in this post, has nothing to do with dropbox.

 

[philou54520]

In that case, would this suffice?
Formula in B4, copied down

Dilution

	A	B
Stock solution concentration
Dilutional factor
Number of dilutions

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:214px;"><col style="width:56px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]

[TD="align: right"]5000[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]

[TD="align: right"]2[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]

[TD="align: right"]7[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]

[TD="align: right"]5000[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]

[TD="align: right"]2500[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]

[TD="align: right"]1250[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]

[TD="align: right"]625[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]8[/TD]

[TD="align: right"]312.5[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]9[/TD]

[TD="align: right"]156.25[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]10[/TD]

[TD="align: right"]78.125[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]11[/TD]

[TD="align: right"]39.0625[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]12[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula B4 =IF(ROWS(B$4:B4)>B$3+1,"",B$1/B$2^(ROWS(B$4:B4)-1)) <tbody> </tbody>

<tbody>
</tbody>

Yeah!! sure that's it! as simple as that! Thank you very much for your help! Finally, I assume that array (matrix) was not the right choice then!

Thx

 

[Peter_SSs]

Thank you very much for your help!

You're welcome.