Key word formula and do some maths :)

Hey, just wondering is there a formula that allows you to search a column for a particular word and then from that have it divide a numbe rin another column by 2 or 3 depending on the word?

Thanks

So are you saying that if it finds the word in cell B12, say, the formula would then take the number from A12 (i.e. corresponding row) and divide it by 2 or 3? Is this for every cell in the range? Is the word a variable (i.e. in some other cell) or fixed?

Please supply further details.

Pete

You can use Vlookup for the main part of it..

=VLOOKUP("particular word",A:B,2,FALSE)/2

It searches column A for a particular word, and returns the corresponding value from column B (another column)
Then devides by 2

You'll need to provide some more information on how to choose deviding by 2 or 3 depending on the word..
How exactly should we choose to devide by 2 or 3 ?

Basically, I have list of words in Column H that are consist of the following (A, A/B, A/B/C)/ Depending on what the word is I want it to divide a cell in P by 1,2 or 3. For example, it searches H12 and finds A/B, it then divides the number in P12 by 2 in the line row underneath the number. I dont mind manually putting in the rows but I have a giant list and it will take me a week to divide everything.


Thanks so much for your help

Sean

Sorry, that description didn't help at all.
Still don't understand why it's devided by 2 instead of 1 or 3

Can you post a sample book?

Book1.xlsxSorry, I honestly am clueless when it comes to excel.

Ok in the example below I have to divide the cost in col, J, by whatever amount of items are in col. F. So in row 2 I have one laptop so, that cost will stay as normal. In row 3 I have 3 items so I need to divide the cost (col. J) by 3. Ideally I would love a macro to insert rows under neath the rows that have more than 2 item and then do the maths in that row under the cost column but I dont think it is possible. The same idea would work for 2 items.

Again sorry for all the confusion and if this is not possible than I will just do it manually. ( Looking for an easy way out

Thanks

Try

=J2/(LEN(F2)-LEN(SUBSTITUTE(F2,"/",""))+1)

It doesn't help that you keep changing the cell references, and the layout of the data - at first you were asking for the result to be in the row below where the data is. It's also a very strange logic that you are applying - the average cost per item. So if you bought a laptop and a separate mouse, you would average the cost for both items?

Pete