To see less ads Register or Login ----- Daily Fantasy Sports games 18+

Function help?

A forum to discuss or share knowledge of spreadsheets.
User avatar
Jester
Sir Jedi Jesterlot
Posts: 25092
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:

Function help?

Post by Jester »

I've just started using Excel after about six years or so and am struggling to remember much of it....


Today I have the following problem to solve:

Code: Select all

Date:       wk1   wk2   wk3   wk4   wk5
Donuts:      50    30   15    -5    -20

I want to record when I run out of donuts on another worksheet, in this case wk4

I have lots of other items, not just donuts, so I need to lookup donuts, and report the week stocks go negative on the other sheet.


Hope that makes sense?

If anyone can help I would be most apreciative! :D

User avatar
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

Re: Function help?

Post by Surprised »

Probably a combination of a couple of functions.
I'll have a look after lunch.

User avatar
murf
FISO Viscount
Posts: 109450
Joined: 13 Oct 2005, 18:28
Location: here
FS Record: Once led TFF. Very briefly.
Contact:

Re: Function help?

Post by murf »

Can you do it using the same grid - if so each square on the second sheet just needs a

IF ([other sheet]A1<0,[other sheet]A1,"")

then most squares will be blank and your -ves will appear

User avatar
Judio
Dumbledore
Posts: 9636
Joined: 13 Oct 2005, 18:28
Location: Surrey

Re: Function help?

Post by Judio »

Or you could use a Conditional Format

To Highlight in Red when stock goes below a certain figure

This would be better than waiting until it was ZERO

User avatar
Jester
Sir Jedi Jesterlot
Posts: 25092
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?

Post by Jester »

I'm already using conditional formating to warn me when stocks are low.

It's not a similar grid on the second worksheet, it's a summary sheet so just one column reporting the date we run out of stock. I could create another (working) sheet but I think I would still have the same problem as now?

Apologies for being slow to reply but I'm currently going through the sheet manually, and it's bigger slow!!! :lol:


Anyone good with SAP?!! :?

User avatar
murf
FISO Viscount
Posts: 109450
Joined: 13 Oct 2005, 18:28
Location: here
FS Record: Once led TFF. Very briefly.
Contact:

Re: Function help?

Post by murf »

Could you use a working sheet to 'sort' all your data in order (lowest first) then your proper sheet 2 could just read the top of that sorted list (using IFs you could avoid showing +ves)

User avatar
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

Re: Function help?

Post by Surprised »

Index with Match function will work
ie
=INDEX(weeks,MATCH(1,INDEX(--(donuts<=0),0),0))

you will need to name the ranges but it will work then for any number of items
I'll post details a bit later if needed

User avatar
Jester
Sir Jedi Jesterlot
Posts: 25092
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?

Post by Jester »

Surprised wrote:Index with Match function will work
ie
=INDEX(weeks,MATCH(1,INDEX(--(donuts<=0),0),0))

you will need to name the ranges but it will work then for any number of items
I'll post details a bit later if needed


Aha! Sounds interesting.
I've never worked with Index or Match, so might need to be spoon fed here....
I'll have a quick Google to see what I can learn.

User avatar
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

Re: Function help?

Post by Surprised »

Jester wrote:
Surprised wrote:Index with Match function will work
ie
=INDEX(weeks,MATCH(1,INDEX(--(donuts<=0),0),0))

you will need to name the ranges but it will work then for any number of items
I'll post details a bit later if needed


Aha! Sounds interesting.
I've never worked with Index or Match, so might need to be spoon fed here....
I'll have a quick Google to see what I can learn.
I will give details in a few mins

User avatar
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

Re: Function help?

Post by Surprised »

Code: Select all

Date:       wk1   wk2   wk3   wk4   wk5
Donuts:      50    30   15    -5    -20
Pies:        23    22   -14    22    -3
So let's assume these are in cells A3 to F5

You will need to name the ranges for as many product lines as you have and a range for the weeks
So in this example I have named ranges as follows
weeks b3 to f3
donuts b4 to f4
pies b5 to f5

So,in a new sheet put this for instance in cells a2 and a3
donuts
pies

In b2 put this formula =INDEX(weeks,MATCH(1,INDEX(--(donuts<=0),0),0))
in cell b3 put this formula =INDEX(weeks,MATCH(1,INDEX(--(pies<=0),0),0))

Then it will tell you the first week that the stock goes to zero or lower

I can send you the sample spreadsheet if you like.

User avatar
Jester
Sir Jedi Jesterlot
Posts: 25092
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?

Post by Jester »

I'm trying it... Might be a while! :?




It works! :D

I need to finish the manual entry stuff I'm doing right now, then I can try to apply this to the actual workbook.

I'll let you know how I get on - thanks for taking the time to help!

User avatar
Jester
Sir Jedi Jesterlot
Posts: 25092
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?

Post by Jester »

I've had a quick play and it looks like just what I needed!

Thanks FISO and thanks Surprised! :D



No doubt I will be back again with another one soon....?!! :?

User avatar
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

Re: Function help?

Post by Surprised »

Your welcome

User avatar
Jester
Sir Jedi Jesterlot
Posts: 25092
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?

Post by Jester »

Question 2 on functions.....


VLOOKUP in fact.

All I want to do is copy a vlookup formula across several columns with just the column index increasing by one in each subsequent column.

ie. =VLOOKUP($B4,[Workbook1.xls]Sheet1!$A$1:$BA$100,12,FALSE)

So the first result would be the value from column 12 of the table array, the second column 13 and so on.
The lookup value and table arrary are absolute, hence the Dollar signs. Dragging the formula across multiple columns however, doesn't see the column index number increasing.



It must be easy to do - at the moment I'm editing the formula in each column manually....


Cheers for any help!

User avatar
unc.si.
FISO Knight
Posts: 11775
Joined: 11 Oct 2010, 14:08
Location: Off to buy Loctite
FS Record: 'Loser' by Beck

Re: Function help?

Post by unc.si. »

an easy way is to replace the '12' with a cell reference (eg c5). Put 12 in C5, 13 in d5, 14 in e5 etc etc). Copy the formula across and bobs yer.

=VLOOKUP($B4,[Workbook1.xls]Sheet1!$A$1:$BA$100,C$5,FALSE), where C5 = 12, D5 = 13 etc

User avatar
Jester
Sir Jedi Jesterlot
Posts: 25092
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?

Post by Jester »

Brilliant unc - thanks! :D


I've been trying to get

=VLOOKUP($A4,Workbook1!$A$1:$F$200,COLUMNS($B4:B4)+1,FALSE)

to work without success.

Your method I understand though. Nice one :D

User avatar
unc.si.
FISO Knight
Posts: 11775
Joined: 11 Oct 2010, 14:08
Location: Off to buy Loctite
FS Record: 'Loser' by Beck

Re: Function help?

Post by unc.si. »

Jester wrote:Brilliant unc - thanks! :D


I've been trying to get

=VLOOKUP($A4,Workbook1!$A$1:$F$200,COLUMNS($B4:B4)+1,FALSE)

to work without success.

Your method I understand though. Nice one :D
It's not a particularly technical or clever solution, but its quick, easy to explain and it works :lol:

Yours should work as well though, although surely it should be +11, not +1?

edit: i.e. =VLOOKUP($A4,Workbook1!$A$1:$F$200,COLUMNS($B4:B4)+11,FALSE), assuming your data starts in column 12

User avatar
Jester
Sir Jedi Jesterlot
Posts: 25092
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?

Post by Jester »

You're right - I understand that formula as well now.
I didn't realise why it was +1......

Thanks again! :D

hancockjr
Dumbledore
Posts: 7976
Joined: 17 Aug 2006, 21:24
FS Record: FPL: Not as good as it was, but still very respectable.

Re: Function help?

Post by hancockjr »

The best way is probably to have the number replaced by a match function and the relevant column heading on the database replicated above the answer sheet. The match looks for how many columns along the database the answer sheet title is Has the benefit that if you add columns into your database it still picks the right one up. On iPhone so cannot be more explicit - exercise left to reader!

User avatar
Jester
Sir Jedi Jesterlot
Posts: 25092
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?

Post by Jester »

I'm going to have to read up on this index/match function as it seems it would be ideal in much of what I struggle to do with vlookup?

I used it as suggested by Surprised above but didn't fully understand how it works...

User avatar
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

Re: Function help?

Post by Surprised »

Jester wrote:I'm going to have to read up on this index/match function as it seems it would be ideal in much of what I struggle to do with vlookup?

I used it as suggested by Surprised above but didn't fully understand how it works...
It's like using vlookup or hlookup but when you don't know which column or row the item you want is located.
But that doesn't really explain :oops:

User avatar
Jester
Sir Jedi Jesterlot
Posts: 25092
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?

Post by Jester »

I'll have a play tomorrow. I think the named ranges confused me a bit as I don't often use them, but you don't have to name ranges do you - as with vlookup, it just makes things a bit more tidy?

User avatar
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

Re: Function help?

Post by Surprised »

There is no need to name ranges.
I never used to bother but it does make it a lot easier to keep track of things.

Ie if you see $A$1:$D$20 in a formula it means nothing but if you see "donuts' in a formula you will know what cells it refers to and why.

hancockjr
Dumbledore
Posts: 7976
Joined: 17 Aug 2006, 21:24
FS Record: FPL: Not as good as it was, but still very respectable.

Re: Function help?

Post by hancockjr »

But named ranges don't copy; when you copy the formula it is still donuts, which is tedious. Avoid them.

User avatar
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

Re: Function help?

Post by Surprised »

hancockjr wrote:But named ranges don't copy; when you copy the formula it is still donuts, which is tedious. Avoid them.
It will work if the named range is referenced in a cell rather than hard coded in the formula.

It's really just an alternative to using absolute cell references

hancockjr
Dumbledore
Posts: 7976
Joined: 17 Aug 2006, 21:24
FS Record: FPL: Not as good as it was, but still very respectable.

Re: Function help?

Post by hancockjr »

Surprised wrote:
hancockjr wrote:But named ranges don't copy; when you copy the formula it is still donuts, which is tedious. Avoid them.
It will work if the named range is referenced in a cell rather than hard coded in the formula.

It's really just an alternative to using absolute cell references
If named ranges are referenced in a cell rather than hard coded, don't you just have to reference those named ramges in the way you would the range you wnated anyway, so you've gone to some trouble but for no advantage?

User avatar
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

Re: Function help?

Post by Surprised »

hancockjr wrote:
Surprised wrote:
hancockjr wrote:But named ranges don't copy; when you copy the formula it is still donuts, which is tedious. Avoid them.
It will work if the named range is referenced in a cell rather than hard coded in the formula.

It's really just an alternative to using absolute cell references
If named ranges are referenced in a cell rather than hard coded, don't you just have to reference those named ramges in the way you would the range you wnated anyway, so you've gone to some trouble but for no advantage?
Not always. Say you have a range called 'donuts' then you reference the cell with the name 'donuts' in it. Whenever you add rows or columns to the named range it then changes.

User avatar
Jester
Sir Jedi Jesterlot
Posts: 25092
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?

Post by Jester »

Question 3:

I can remember doing something like this during my training around 20 years ago.....
Bit of a block now though!
Q3.jpg
I want to know when to order each item.
Assuming current stock is the same as lot size in this instance, therefore I will run out of item A in wk3 and therefore need to order more of it.

It's a SUM function isn't it - If the sum of C3 to E3 is greater than or equal to lot size, place an order.
It's calculating the next order point that is really testing me, I need to add the surplus from wk3 to wk 4 etc for the next order point
ie. In the example at the end of wk 3 I will have used 120 of Item A therefore:

(100-(20+50+50))+100 = 80 remaining; => 80+150=230, so need to order 2xlot size in wk4 and so on....

Hope it's not too difficult?! :?
You do not have the required permissions to view the files attached to this post.

User avatar
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

Re: Function help?

Post by Surprised »

It's not just a Sum or SUMIF statement as the reorder can come in any week. It's probably a combination of SUM, IF and OR.
So with product A it will check is week 1 more than 100 then reorder. If not then is week 1+week 2 more than 100. And so on.

I will have a look over the weekend.

User avatar
WilBert
Dumbledore
Posts: 7767
Joined: 28 Dec 2009, 21:07
Location: 2nd in SP4's Ashes comp.

Re: Function help?

Post by WilBert »

Image

I like to simplify things as much as I can, rather than have complex formulas that a colleague can't understand if something goes wrong; I break things down by hiding rows/columns use seperate sheet for workings out etc.

In this example you would have to change the week number at the top (or use auto dates), fill in the estimated usage for a forward period then fill in the actual usage on a weekly basis. The Hlookup finds the right week to work out the maths and the conditional format turns the cell red for you.

Maybe too simplistic a solution for you because you are simpifying the problem for post purposes.

View Latest: 1 Day View Your posts
Post Reply

Return to “Spreadsheets”