Function help?
- Jester
- Sir Jedi Jesterlot
- Posts: 25201
- Joined: 13 Oct 2005, 18:28
- Location: Back at FISODAS Tower!
- FS Record: TFC 2008 T20 winner; EggCup Div 1 06/07; Two-Foot Cup 2018; S4 EPL Cup 2020
- Contact:
Re: Function help?
That looks good WilBert, and I can actually follow it OK!
The thing is, I want it to show when and how much to order, not necessarily what stocks are left.
I think I can use your method though, if I don't find something
The thing is, I want it to show when and how much to order, not necessarily what stocks are left.
I think I can use your method though, if I don't find something
- Rob
- Grumpy Old Man
- Posts: 3639
- Joined: 24 Jul 2009, 10:54
- Location: Poster formerly known as SOBD
- FS Record: WC2010 FISODAS Champion!!
- FPL:
Re: Function help?
I'd insert a blank row below row 3 and in cell b4 have:
=IF(ROUNDUP((SUM($B3:C3)-SUM($A4:A4))/100,0)<0,0,(ROUNDUP((SUM($B3:C3)-SUM($A4:A4))/100,0))*$B$2)
=IF(ROUNDUP((SUM($B3:C3)-SUM($A4:A4))/100,0)<0,0,(ROUNDUP((SUM($B3:C3)-SUM($A4:A4))/100,0))*$B$2)
- Jester
- Sir Jedi Jesterlot
- Posts: 25201
- Joined: 13 Oct 2005, 18:28
- Location: Back at FISODAS Tower!
- FS Record: TFC 2008 T20 winner; EggCup Div 1 06/07; Two-Foot Cup 2018; S4 EPL Cup 2020
- Contact:
Re: Function help?
Cheers SOBD that works, but it doesn't allow zero stock.
ie. Item B
Stock = 100
Wk1 req = 300
I therefore need to order 200, whereas the formula tells me I need to order 300.
I can't immediately see what I need to change in your formula to get what I want.....?
ie. Item B
Stock = 100
Wk1 req = 300
I therefore need to order 200, whereas the formula tells me I need to order 300.
I can't immediately see what I need to change in your formula to get what I want.....?
- Rob
- Grumpy Old Man
- Posts: 3639
- Joined: 24 Jul 2009, 10:54
- Location: Poster formerly known as SOBD
- FS Record: WC2010 FISODAS Champion!!
- FPL:
Re: Function help?
I've initially assumed that B1 is the batch size and there is zero opening stock.
Also, the formula assumes a lead time of 1 week, so the formula result in C4 shows the amount to be ordered in week 1 to be able to satisfy the demand in week 2.
To answer your qeustion, to represent an opening stock figure I have added -100 to cell B3 and 300 in cell C3. The result in B4 is then 200 i.e. order 200 in week 0 so that there is enough for week 1.
Also, the formula assumes a lead time of 1 week, so the formula result in C4 shows the amount to be ordered in week 1 to be able to satisfy the demand in week 2.
To answer your qeustion, to represent an opening stock figure I have added -100 to cell B3 and 300 in cell C3. The result in B4 is then 200 i.e. order 200 in week 0 so that there is enough for week 1.
- Jester
- Sir Jedi Jesterlot
- Posts: 25201
- Joined: 13 Oct 2005, 18:28
- Location: Back at FISODAS Tower!
- FS Record: TFC 2008 T20 winner; EggCup Div 1 06/07; Two-Foot Cup 2018; S4 EPL Cup 2020
- Contact:
Re: Function help?
I'm sorry, think I was still asleep when I replied before....?
It works well enough ASIS - what I'm looking at isn't actual stock, but stock required to meet predicted demand. It's perfectly right to assume a starting figure of zero.
Thanks again!
(I don't wholly understand the formula, but I think I will get there! )
It works well enough ASIS - what I'm looking at isn't actual stock, but stock required to meet predicted demand. It's perfectly right to assume a starting figure of zero.
Thanks again!
(I don't wholly understand the formula, but I think I will get there! )
- Jester
- Sir Jedi Jesterlot
- Posts: 25201
- Joined: 13 Oct 2005, 18:28
- Location: Back at FISODAS Tower!
- FS Record: TFC 2008 T20 winner; EggCup Div 1 06/07; Two-Foot Cup 2018; S4 EPL Cup 2020
- Contact:
Re: Function help?
Here we go again!
This one should be easy, but apparantly it isn't......
I have a column of numbers, with gaps in some rows at irregular intervals:
24
15
6
99
123
2
SPACE
14
7
45
SPACE
etc....
I want to add a total (or subtotal) in every space - totalling the numbers since the last space/total.
That's all
I though I could just highlight the column and select data/subtotal, but that needs a reference column.
I could add a column nect to the one with the numbers in, which works, but it turns my sheet into a pivot table and looks messy.
Can't it be done more straightforward than that?
Forgot to say there are hundreds of rows, so very time consuming to select every space and add a total that way!
This one should be easy, but apparantly it isn't......
I have a column of numbers, with gaps in some rows at irregular intervals:
24
15
6
99
123
2
SPACE
14
7
45
SPACE
etc....
I want to add a total (or subtotal) in every space - totalling the numbers since the last space/total.
That's all
I though I could just highlight the column and select data/subtotal, but that needs a reference column.
I could add a column nect to the one with the numbers in, which works, but it turns my sheet into a pivot table and looks messy.
Can't it be done more straightforward than that?
Forgot to say there are hundreds of rows, so very time consuming to select every space and add a total that way!
- Surprised
- FISO Jedi Knight
- Posts: 26528
- Joined: 13 Oct 2005, 18:32
- Location: Home
- FS Record: TFFOSM MotW in 2008 and MotM in 2003. 78th overall in TFFO for 2002/3 and 2003/4
- FPL:
Re: Function help?
Why would inserting a column turn it into a pivot table?
- Surprised
- FISO Jedi Knight
- Posts: 26528
- Joined: 13 Oct 2005, 18:32
- Location: Home
- FS Record: TFFOSM MotW in 2008 and MotM in 2003. 78th overall in TFFO for 2002/3 and 2003/4
- FPL:
Re: Function help?
Try this
1) Select the full range of cells with blanks
2) Press F5 and click on Special
3) Select Blanks and click on OK
4) Press the Autosum button
This will put the formula in all blank cells. It wont put a total after the last set of numbers though.
1) Select the full range of cells with blanks
2) Press F5 and click on Special
3) Select Blanks and click on OK
4) Press the Autosum button
This will put the formula in all blank cells. It wont put a total after the last set of numbers though.
- Jester
- Sir Jedi Jesterlot
- Posts: 25201
- Joined: 13 Oct 2005, 18:28
- Location: Back at FISODAS Tower!
- FS Record: TFC 2008 T20 winner; EggCup Div 1 06/07; Two-Foot Cup 2018; S4 EPL Cup 2020
- Contact:
Re: Function help?
Surprised wrote:Why would inserting a column turn it into a pivot table?
That's a very good question.....
There were lines with '+' and '-' against them on the left, misinterpreted that as a pivot table but it just defines the subtotals doesn't it.....
It put the subtotals at the top of each set of numbers as well - why would it do that?!
Reckon it's not subtotals that I need after all.... Just plain and simple totals!
Just seen your next post - giving it a go!
- Surprised
- FISO Jedi Knight
- Posts: 26528
- Joined: 13 Oct 2005, 18:32
- Location: Home
- FS Record: TFFOSM MotW in 2008 and MotM in 2003. 78th overall in TFFO for 2002/3 and 2003/4
- FPL:
Re: Function help?
Remove the subtotals first before you do it.
- Jester
- Sir Jedi Jesterlot
- Posts: 25201
- Joined: 13 Oct 2005, 18:28
- Location: Back at FISODAS Tower!
- FS Record: TFC 2008 T20 winner; EggCup Div 1 06/07; Two-Foot Cup 2018; S4 EPL Cup 2020
- Contact:
Re: Function help?
That works!
Thanks!
How about this:
Can you select several cells in a column, not adjacent ones (the totals I just put in as above in fact!), and copy them across to the next column?
I can't do the next columns in the same way because there are more empty cells than in the first column
eg.
12
33
space
17
1
3
space
space
77
4
total required (of the ten rows above)
That's the last one for today - I hope?!
Thanks!
How about this:
Can you select several cells in a column, not adjacent ones (the totals I just put in as above in fact!), and copy them across to the next column?
I can't do the next columns in the same way because there are more empty cells than in the first column
eg.
12
33
space
17
1
3
space
space
77
4
total required (of the ten rows above)
That's the last one for today - I hope?!
- Surprised
- FISO Jedi Knight
- Posts: 26528
- Joined: 13 Oct 2005, 18:32
- Location: Home
- FS Record: TFFOSM MotW in 2008 and MotM in 2003. 78th overall in TFFO for 2002/3 and 2003/4
- FPL:
Re: Function help?
If the other cells are just numbers and not formulas you can
Do the F5 thing again and click special but this time select "formulas"
Then just copy and paste:special:values
Assuming you want values and not the formula copied?
Do the F5 thing again and click special but this time select "formulas"
Then just copy and paste:special:values
Assuming you want values and not the formula copied?
- Jester
- Sir Jedi Jesterlot
- Posts: 25201
- Joined: 13 Oct 2005, 18:28
- Location: Back at FISODAS Tower!
- FS Record: TFC 2008 T20 winner; EggCup Div 1 06/07; Two-Foot Cup 2018; S4 EPL Cup 2020
- Contact:
Re: Function help?
They're all formulas - didn't make that clear did I....
In the next columns there are different formulas, but I still want to total in the same rows as the first column.
Because the formulas are different I can't just copy the whole column.
In the next columns there are different formulas, but I still want to total in the same rows as the first column.
Because the formulas are different I can't just copy the whole column.
- Surprised
- FISO Jedi Knight
- Posts: 26528
- Joined: 13 Oct 2005, 18:32
- Location: Home
- FS Record: TFFOSM MotW in 2008 and MotM in 2003. 78th overall in TFFO for 2002/3 and 2003/4
- FPL:
Re: Function help?
are you adding the same number of figures?
ir if column A has
12
33
space
17
1
3
space
space
77
4
will column B need to add 2 numbers than add 3 numbers and then add 2 numbers?
ir if column A has
12
33
space
17
1
3
space
space
77
4
will column B need to add 2 numbers than add 3 numbers and then add 2 numbers?
- Jester
- Sir Jedi Jesterlot
- Posts: 25201
- Joined: 13 Oct 2005, 18:28
- Location: Back at FISODAS Tower!
- FS Record: TFC 2008 T20 winner; EggCup Div 1 06/07; Two-Foot Cup 2018; S4 EPL Cup 2020
- Contact:
Re: Function help?
I'm afraid not - completely random.
I think I might be expecting too much with this one....
I can do it manually of course, so don't spend too much time on it!
I think I might be expecting too much with this one....
I can do it manually of course, so don't spend too much time on it!
- Jester
- Sir Jedi Jesterlot
- Posts: 25201
- Joined: 13 Oct 2005, 18:28
- Location: Back at FISODAS Tower!
- FS Record: TFC 2008 T20 winner; EggCup Div 1 06/07; Two-Foot Cup 2018; S4 EPL Cup 2020
- Contact:
Re: Function help?
I just found out the F5 blanks thing works with formulae as well - that's going to save me loads of time!!
Thanks again!
Thanks again!
- Surprised
- FISO Jedi Knight
- Posts: 26528
- Joined: 13 Oct 2005, 18:32
- Location: Home
- FS Record: TFFOSM MotW in 2008 and MotM in 2003. 78th overall in TFFO for 2002/3 and 2003/4
- FPL:
Re: Function help?
May be an easier way but this way is quick and it seems to work.
- Jester
- Sir Jedi Jesterlot
- Posts: 25201
- Joined: 13 Oct 2005, 18:28
- Location: Back at FISODAS Tower!
- FS Record: TFC 2008 T20 winner; EggCup Div 1 06/07; Two-Foot Cup 2018; S4 EPL Cup 2020
- Contact:
Re: Function help?
It's Monday - another week another challenge!
I've created a large worksheet that I now need to pull information from.
I was going to do this with VLOOKUP but have a problem.....
Each item on the sheet has a different number of lines of information.
eg.
Item 1 is in A1
The information I need to lookup is a total in, say D5
Item 2 is therefore in A6 (Left a blank row between each item)
Information required is in D16
Item 3 is in A18
Information in D21
etc.
Can I do this with vlookup or is there a better way?
Will I need to make each item take up the same number of rows (by adding empty rows for instance)
I hope it can be done....?
I've created a large worksheet that I now need to pull information from.
I was going to do this with VLOOKUP but have a problem.....
Each item on the sheet has a different number of lines of information.
eg.
Item 1 is in A1
The information I need to lookup is a total in, say D5
Item 2 is therefore in A6 (Left a blank row between each item)
Information required is in D16
Item 3 is in A18
Information in D21
etc.
Can I do this with vlookup or is there a better way?
Will I need to make each item take up the same number of rows (by adding empty rows for instance)
I hope it can be done....?
- murf
- FISO Viscount
- Posts: 109611
- Joined: 13 Oct 2005, 18:28
- Location: here
- FS Record: Once led TFF. Very briefly.
- FPL:
- Contact:
Re: Function help?
If the 'total' is the maximum (highest) number in the range then why not create a new cell (in line with A1 etc? - E1??) that looks for the maximum in a range. You can then VLOOKUP in column E.
- Mystery
- FISO Knight
- Posts: 13816
- Joined: 13 Oct 2005, 18:33
- Location: Just about here
- FS Record: 116th in TFF 05/06
- FPL:
- Contact:
Function help?
If there's a differentiating column, say, the month or something in another column then you could subtotal any other column.
- Jester
- Sir Jedi Jesterlot
- Posts: 25201
- Joined: 13 Oct 2005, 18:28
- Location: Back at FISODAS Tower!
- FS Record: TFC 2008 T20 winner; EggCup Div 1 06/07; Two-Foot Cup 2018; S4 EPL Cup 2020
- Contact:
Re: Function help?
murf wrote:If the 'total' is the maximum (highest) number in the range then why not create a new cell (in line with A1 etc? - E1??) that looks for the maximum in a range. You can then VLOOKUP in column E.
I was thinking I might have to add a new column to lookup, but I was going to put the totals in it rather than look up the highest number in a range - same thing really isn't it?
That's a lot of work though, so I'd prefer another option!
Mystery wrote:If there's a differentiating column, say, the month or something in another column then you could subtotal any other column.
I don't understand what you mean? All the columns have headers, and the column I need is already totaled - just not totaled on the same row as the item name that I'm looking up. If I move the totals to the same row as the item name, I think that's the same solution as murf's?
(Hope that makes sense....?)
Think I should have planned my sheet a bit better from the start!?
- Surprised
- FISO Jedi Knight
- Posts: 26528
- Joined: 13 Oct 2005, 18:32
- Location: Home
- FS Record: TFFOSM MotW in 2008 and MotM in 2003. 78th overall in TFFO for 2002/3 and 2003/4
- FPL:
Re: Function help?
If the total is always in column D then vlookup will work if the corresponding entry in column A is unique.
Ie A5 nay be "Item A total" with the figure you want in D5
A21 may be "Item B total" with the actual total in D21 and so on
Ie A5 nay be "Item A total" with the figure you want in D5
A21 may be "Item B total" with the actual total in D21 and so on
- Jester
- Sir Jedi Jesterlot
- Posts: 25201
- Joined: 13 Oct 2005, 18:28
- Location: Back at FISODAS Tower!
- FS Record: TFC 2008 T20 winner; EggCup Div 1 06/07; Two-Foot Cup 2018; S4 EPL Cup 2020
- Contact:
Re: Function help?
Surprised wrote:If the total is always in column D then vlookup will work if the corresponding entry in column A is unique.
Ie A5 nay be "Item A total" with the figure you want in D5
A21 may be "Item B total" with the actual total in D21 and so on
So I need an entry in Column A in the same row as the total I'm looking up?
I was hoping there was a way of referencing the total to the item name, but wasn't hopeful considering there are variable numbers of row for each item.
A further problem I have is that the sheet the lookup value needs to be added to is not mine and the items are already listed. Therefore names are not 'Item A total' etc but 'Item A'
I think I will need to create new intermediate lookup sheets to get around this?
- Rob
- Grumpy Old Man
- Posts: 3639
- Joined: 24 Jul 2009, 10:54
- Location: Poster formerly known as SOBD
- FS Record: WC2010 FISODAS Champion!!
- FPL:
Re: Function help?
It is possible but would be quite an involved formula. I'd be able to provide an example this evening if still required.
Is it just the item decriptions in column A? As per your example, would the cells A2 to A5 be blank?
Is it just the item decriptions in column A? As per your example, would the cells A2 to A5 be blank?
- Jester
- Sir Jedi Jesterlot
- Posts: 25201
- Joined: 13 Oct 2005, 18:28
- Location: Back at FISODAS Tower!
- FS Record: TFC 2008 T20 winner; EggCup Div 1 06/07; Two-Foot Cup 2018; S4 EPL Cup 2020
- Contact:
Re: Function help?
SOBD wrote:It is possible but would be quite an involved formula. I'd be able to provide an example this evening if still required.
Is it just the item decriptions in column A? As per your example, would the cells A2 to A5 be blank?
Yes, just the description in col A - all other rows are empty.
The totals are also the only used cell in their particular rows, and there is a completely blank row between each item (not that this will make much difference I guess?)
It seems to me the problem is complicated by the fact there are different numbers of rows for each item. If that's the case, I can make them all the same using blank rows. That would make the sheet somewhat untidy but I could live with that if it helps? Maybe there is a quick way to do this too...?!!
- Mystery
- FISO Knight
- Posts: 13816
- Joined: 13 Oct 2005, 18:33
- Location: Just about here
- FS Record: 116th in TFF 05/06
- FPL:
- Contact:
Function help?
Can you post a screen shot. It sounds achievable but don't totally understand the question.
- Rob
- Grumpy Old Man
- Posts: 3639
- Joined: 24 Jul 2009, 10:54
- Location: Poster formerly known as SOBD
- FS Record: WC2010 FISODAS Champion!!
- FPL:
Re: Function help?
Based on the above info it can be achieved using the offset and match functions.Jester wrote:Yes, just the description in col A - all other rows are empty.
The totals are also the only used cell in their particular rows, and there is a completely blank row between each item (not that this will make much difference I guess?)
It seems to me the problem is complicated by the fact there are different numbers of rows for each item. If that's the case, I can make them all the same using blank rows. That would make the sheet somewhat untidy but I could live with that if it helps? Maybe there is a quick way to do this too...?!!
If the data is in Sheet1 and you have the decriptions Item 1, Item 2, Item 3, etc in cells B3, B4, B5, etc of another sheet then the formula to pull though Item 1's formula would be:
=OFFSET(Sheet1!$A$1,MATCH(B4,Sheet1!A:A,0)-MATCH($B$3,Sheet1!A:A,0)-2,3)
Item 2's formula:
=OFFSET(Sheet1!$A$1,MATCH(B5,Sheet2!A:A,0)-MATCH($B$3,Sheet2!A:A,0)-2,3)
Hope that makes sense.
- Surprised
- FISO Jedi Knight
- Posts: 26528
- Joined: 13 Oct 2005, 18:32
- Location: Home
- FS Record: TFFOSM MotW in 2008 and MotM in 2003. 78th overall in TFFO for 2002/3 and 2003/4
- FPL:
Re: Function help?
SOBD wrote:Based on the above info it can be achieved using the offset and match functions.Jester wrote:Yes, just the description in col A - all other rows are empty.
The totals are also the only used cell in their particular rows, and there is a completely blank row between each item (not that this will make much difference I guess?)
It seems to me the problem is complicated by the fact there are different numbers of rows for each item. If that's the case, I can make them all the same using blank rows. That would make the sheet somewhat untidy but I could live with that if it helps? Maybe there is a quick way to do this too...?!!
If the data is in Sheet1 and you have the decriptions Item 1, Item 2, Item 3, etc in cells B3, B4, B5, etc of another sheet then the formula to pull though Item 1's formula would be:
=OFFSET(Sheet1!$A$1,MATCH(B4,Sheet1!A:A,0)-MATCH($B$3,Sheet1!A:A,0)-2,3)
Item 2's formula:
=OFFSET(Sheet1!$A$1,MATCH(B5,Sheet2!A:A,0)-MATCH($B$3,Sheet2!A:A,0)-2,3)
Hope that makes sense.
OFFSET never even occurred to me
Clever solution.
- Jester
- Sir Jedi Jesterlot
- Posts: 25201
- Joined: 13 Oct 2005, 18:28
- Location: Back at FISODAS Tower!
- FS Record: TFC 2008 T20 winner; EggCup Div 1 06/07; Two-Foot Cup 2018; S4 EPL Cup 2020
- Contact:
Re: Function help?
Mystery wrote:Can you post a screen shot. It sounds achievable but don't totally understand the question.
Can't do that I'm afraid - sensitive information you know!!
SOBD wrote:Based on the above info it can be achieved using the offset and match functions.Jester wrote:Yes, just the description in col A - all other rows are empty.
The totals are also the only used cell in their particular rows, and there is a completely blank row between each item (not that this will make much difference I guess?)
It seems to me the problem is complicated by the fact there are different numbers of rows for each item. If that's the case, I can make them all the same using blank rows. That would make the sheet somewhat untidy but I could live with that if it helps? Maybe there is a quick way to do this too...?!!
If the data is in Sheet1 and you have the decriptions Item 1, Item 2, Item 3, etc in cells B3, B4, B5, etc of another sheet then the formula to pull though Item 1's formula would be:
=OFFSET(Sheet1!$A$1,MATCH(B4,Sheet1!A:A,0)-MATCH($B$3,Sheet1!A:A,0)-2,3)
Item 2's formula:
=OFFSET(Sheet1!$A$1,MATCH(B5,Sheet2!A:A,0)-MATCH($B$3,Sheet2!A:A,0)-2,3)
Hope that makes sense.
No sense whatsoever!
I'm tied up with something else now but will look at it as soon as I get a chance.
Thanks so much for trying to help.
I'll let you know when I've given it a go (and probably failed to get it to work!)
- Jester
- Sir Jedi Jesterlot
- Posts: 25201
- Joined: 13 Oct 2005, 18:28
- Location: Back at FISODAS Tower!
- FS Record: TFC 2008 T20 winner; EggCup Div 1 06/07; Two-Foot Cup 2018; S4 EPL Cup 2020
- Contact:
Re: Function help?
I finally got around to trying this and have to say I've got nowhere....?!
=OFFSET(Sheet1!$A$1,MATCH(B4,Sheet1!A:A,0)-MATCH($B$3,Sheet1!A:A,0)-2,3)
I can get to the column I need by changing the 3 at the end to 14 (the total is in col O), but I can't get the right row.
I can't see anything else other than the '-2' that can be a variable. If I change that I get somewhat lost as to what the result refers to.... What does the '-2' do
=OFFSET(Sheet1!$A$1,MATCH(B4,Sheet1!A:A,0)-MATCH($B$3,Sheet1!A:A,0)-2,3)
I can get to the column I need by changing the 3 at the end to 14 (the total is in col O), but I can't get the right row.
I can't see anything else other than the '-2' that can be a variable. If I change that I get somewhat lost as to what the result refers to.... What does the '-2' do
View Latest: 1 Day View Your posts