Be Excellent To Each Other

And, you know, party on. Dude.

All times are UTC [ DST ]




Reply to topic  [ 114 posts ]  Go to page Previous  1, 2, 3
Author Message
 Post subject: Re: Excel help
PostPosted: Tue Feb 18, 2020 20:53 
User avatar
Comfortably Dumb

Joined: 30th Mar, 2008
Posts: 11508
Location: Sunny Stoke
Cras wrote:
Also demonstrating serious proficiency with excel usually involves saying "don't use Excel for this"


One of my colleagues was on the phone with someone from Microsoft support today because something we were doing was working and we couldn't see how it was possible and their response was basically 'That shouldn't work :shrug: '

_________________
Consolemad | Under Logic
Curse, the day is long
Realise you don't belong


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Wed Feb 19, 2020 0:43 
User avatar
Can you dig it?

Joined: 5th Apr, 2008
Posts: 3726
Cras wrote:
Also demonstrating serious proficiency with excel usually involves saying "don't use Excel for this"


Yeah, I get what you're saying and it's an amusing way to phrase it - Excel is a tool that isn't suitable for all purposes BUT it is still very capable and for many is a familiar way of working so I do see why a lot of people use it as their first go-to (indeed, I do)

_________________
rumours about the high quality of the butter reached Yerevan


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Wed Feb 19, 2020 8:29 
User avatar

Joined: 30th Mar, 2008
Posts: 15677
Yeah, it can get out of hand, though. Quite a few of the things we've done at work are to replace some insane excel solution that's causing issues, but then there were probably bigger issues that got solved by their ad-hoc solution so I can see both sides.


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Wed Feb 19, 2020 9:12 
User avatar
Gogmagog

Joined: 30th Mar, 2008
Posts: 46216
Location: Cheshire
This isn't the talk of winners, here, lads.

_________________
Mr Chris wrote:
MaliA isn't just the best thing on the internet - he's the best thing ever.


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Mon Jul 27, 2020 15:03 
User avatar
Can't re-member

Joined: 27th Mar, 2008
Posts: 54701
Location: Liberty City
There must be a more efficient way of doing this. Any idea? I'm rubbish at Excel.

Code:
=((I2*C2)+(I3*C3)+(I4*C4)+(I5*C5)+(I6*C6)+(I7*C7)+(I8*C8)+(I9*C9)+(I10*C10)+(I11*C11)+(I12*C12)+(I13*C13)+(I14*C14))/C15

_________________
I am currently under construction.
Thank you for your patience.


Image


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Mon Jul 27, 2020 15:24 
SupaMod
User avatar
Commander-in-Cheese

Joined: 30th Mar, 2008
Posts: 48481
You could have a column J where J2=I2*C2 then copy that formula all the way down. Then at the bottom of J (which is presumably J15) have the formula sum(J2:J14) then have a cell that is just J15/C15

_________________
GoddessJasmine wrote:
Drunk, pulled Craster's pork, waiting for brdyime story,reading nuts. Xz


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Mon Jul 27, 2020 15:24 
User avatar
Can't re-member

Joined: 27th Mar, 2008
Posts: 54701
Location: Liberty City
Oh yeah, that’s nice. Thanks.

_________________
I am currently under construction.
Thank you for your patience.


Image


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Fri Jul 31, 2020 17:21 
User avatar

Joined: 27th Mar, 2008
Posts: 21842
Apologies for this being a dumb-as-rocks question, especially as my entire job used to revolve around Excel and I was pretty damned good with it, but having a baby turned my brain to potato.

I am making a meal planner (again, potato brain) with drop downs that feet in from the secondary sheets.

I want a text field in a cell to only show if another cell is populated by text from one of the options in a drop down. Does that make sense? Maybe a screenshot will explain better than I can.

Attachment:
excel screen.png


So, where it says 'with' or 'and' - I only want to show those if the thing has an accompaniment. So Bagel WITH cheese and ham, but not French Toast WITH <blank> - I just want that to say 'French Toast'.

It's in this kiddy's learning font because I'm making it for a little'un.


You do not have the required permissions to view the files attached to this post.

_________________
Image


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Fri Jul 31, 2020 17:27 
User avatar
Isn't that lovely?

Joined: 30th Mar, 2008
Posts: 9621
Location: Devon
I would make a table with the main part of meal in one column and the extras in subsequent column and then use vlookup I think

_________________
Where's the Kaboom? I was expecting an Earth shattering Kaboom!


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Fri Jul 31, 2020 17:35 
User avatar

Joined: 27th Mar, 2008
Posts: 21842
It’s ok, I’ve got it. Thank you though x

Attachment:
Screen Shot 2020-07-31 at 17.55.17.png


You do not have the required permissions to view the files attached to this post.

_________________
Image


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Fri Jul 31, 2020 17:36 
User avatar
ugvm'er at heart...

Joined: 4th Mar, 2010
Posts: 21469
Assuming I've got what you were trying, i'd do something like this:

Code:
=IF(B5<>"","with","")


in the "with" boxes, and something similar in the "and" boxes. B5 being the cell under French Toast in this example, so if B5 is not empty, it shows with, if it is empty it doesn't show anything.

I think, haven't actually fired up a spreadsheet to test it.


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Fri Jul 31, 2020 17:42 
User avatar
Isn't that lovely?

Joined: 30th Mar, 2008
Posts: 9621
Location: Devon
Attachment:
formimi.png


Something like that

I have called the first table "mains" and then in the cell to the right of French Toast, Sanwich Thin and Bolognese I have a formular like this:

=VLOOKUP(M4,mains,2,FALSE)

in the cell below that (where the first and would appear) I have

=VLOOKUP(M4,mains,3,FALSE)

and the one below that (where the 2nd would be appear) I have

=VLOOKUP(M4,mains,4,FALSE)


Bit Clunky, but hopefully you get the idea


You do not have the required permissions to view the files attached to this post.

_________________
Where's the Kaboom? I was expecting an Earth shattering Kaboom!


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Fri Jul 31, 2020 17:58 
User avatar

Joined: 27th Mar, 2008
Posts: 21842
Trooper wrote:
Assuming I've got what you were trying, i'd do something like this:

Code:
=IF(B5<>"","with","")


in the "with" boxes, and something similar in the "and" boxes. B5 being the cell under French Toast in this example, so if B5 is not empty, it shows with, if it is empty it doesn't show anything.

I think, haven't actually fired up a spreadsheet to test it.


Roundabout:
Code:
=IF(B5="","","with")

_________________
Image


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Fri Jul 31, 2020 17:59 
User avatar

Joined: 27th Mar, 2008
Posts: 21842
Malc wrote:
Attachment:
formimi.png


Something like that

I have called the first table "mains" and then in the cell to the right of French Toast, Sanwich Thin and Bolognese I have a formular like this:

=VLOOKUP(M4,mains,2,FALSE)

in the cell below that (where the first and would appear) I have

=VLOOKUP(M4,mains,3,FALSE)

and the one below that (where the 2nd would be appear) I have

=VLOOKUP(M4,mains,4,FALSE)


Bit Clunky, but hopefully you get the idea


I do, but I went with the other way as I'd already 99% set it up that way, but thank you :)

Honestly, I'm pretty sure my mind is permanent fudge now, but it's definitely been worse since lockdown.

_________________
Image


Top
 Profile  
 
Display posts from previous:  Sort by  
Reply to topic  [ 114 posts ]  Go to page Previous  1, 2, 3

All times are UTC [ DST ]


Who is online

Users browsing this forum: devilman, GazChap, Kern, KovacsC, Majestic-12 [Bot], Mimi, Pod People, Vogons and 0 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Search within this thread:
You are using the 'Ted' forum. Bill doesn't really exist any more. Bogus!
Want to help out with the hosting / advertising costs? That's very nice of you.
Are you on a mobile phone? Try http://beex.co.uk/m/
RIP, Owen.

Powered by a very Grim... version of phpBB © 2000, 2002, 2005, 2007 phpBB Group.