Author Topic: Excel help  (Read 59473 times)

Offline Tomaldinho

  • RAWK Supporter
  • Legacy Fan
  • ******
  • Posts: 7,058
  • camina, camina, con esperanza de tu corazon
Re: The Microsoft Excel Help Thread
« Reply #40 on: December 28, 2012, 10:28:26 am »

Now, this formula will change as you copy it around.


 :wellin

Thanks mate

Seems so simple now ;D
@TomNJones on Twitter

Offline Riquende

  • Taking one for the team by giving one to a lucky mascot? Pix or stfu!! (Although is PC is from the 90s so you'll have to wait a while...)
  • Legacy Fan
  • ******
  • Posts: 4,722
  • Μετρήστε με με μανία
Re: The Microsoft Excel Help Thread
« Reply #41 on: December 29, 2012, 03:25:03 am »
When to use $ in cell references (and knowing what they are in the first place) is a key thing to learn the minute you want to use Excel for anything more than autosums.
"The nicest thing about quotes is that they give us a nodding acquaintance with the originator which is often socially impressive."

~ Kenneth Williams, with whom I'm noddingly acquainted. Socially impressed?

Offline Barneylfc∗

  • Cross-dressing man-bag wielding golfer. Wannabe Mod. Coprophiliac. Would like to buy an airline seat if he could. Known 'grass'. Wants to go home to He-Man
  • RAWK Supporter
  • Legacy Fan
  • ******
  • Posts: 59,484
Re: The Microsoft Excel Help Thread
« Reply #42 on: January 24, 2013, 04:32:58 pm »
This is a Word problem, not Excel.

In a nutshell, I can't close out of Word after I use it. I have to use task manager to shut it down. I can't save 1 document, then work on another unless I do this. It's only the free version that was already installed on the laptop. Word Starter 2010 I think it is. But it's extremely annoying for my bird who uses it quite a lot, and is a bit of a technophobe, when she has to shut the computer down after she saves something in order to work on something else in Word. (I have numerous times showed her how to shut Word down using task manager but she doesnt do it.) I have tried uninstalling an reinstalling but doesn't work. Have tried troubleshooting which also doesnt work. Any help is appreciated.
Craig Burnley V West Ham - WEST HAM WIN - INCORRECT

Offline plasterered

  • Legacy Fan
  • ******
  • Posts: 1,654
  • We all Live in a....
Re: The Microsoft Excel Help Thread
« Reply #43 on: January 24, 2013, 05:29:18 pm »
anyone got a fix for the mis-calculations that occur in excel when autosums are done in columns ? it usally out by a penny but only happens every now and then.

Offline Slick_Beef

  • RAWK's Master Baker
  • RAWK Supporter
  • Legacy Fan
  • ******
  • Posts: 9,087
Re: The Microsoft Excel Help Thread
« Reply #44 on: January 24, 2013, 05:41:47 pm »
anyone got a fix for the mis-calculations that occur in excel when autosums are done in columns ? it usally out by a penny but only happens every now and then.

I don't think it's a mis-calculation, more likely it's related to rounding.  Sometimes a cell might display with the value 3.14 but it really contains the value 3.14159 which can make a difference.

Offline plasterered

  • Legacy Fan
  • ******
  • Posts: 1,654
  • We all Live in a....
Re: The Microsoft Excel Help Thread
« Reply #45 on: January 24, 2013, 08:06:07 pm »
I don't think it can be that as I only input figures based on pounds and pence so always 2 digits after the decimal point.

I tried a google to see if anybody knew and it was a total mine field some people have straight forward solutions and others say this is wrong etc and that you shouldnt change certain settings because it will cause other problems else where but there are pages and pages on it.

drives me bonkers and my accountant too

Offline Craig S

  • KOP CONDUCTOR
  • Legacy Fan
  • ******
  • Posts: 4,979
Re: The Microsoft Excel Help Thread
« Reply #46 on: January 25, 2013, 10:20:57 am »
I don't think it's a mis-calculation, more likely it's related to rounding.  Sometimes a cell might display with the value 3.14 but it really contains the value 3.14159 which can make a difference.

There's definitely a mis-calculation within Excel, i have seen it on occasion. We use figures to 7 decimal places, and we round to 7 d.p before exporting data to Excel or mySQL. When the same calculated results are rounded to 1 d.p in both there is occasionally a 0.1 difference (not very often). When we have investigated, and calculated manually, it's always been Excel that is incorrect.

MS has this below, I'm not sure how that differs on the mySQL server though, but it's LAMP, so may differ storage.
http://support.microsoft.com/kb/214118
Quote
The IEEE 754 standard is a method of storing floating-point numbers in a compact way that is easy to manipulate. This standard is used by Intel coprocessors and most PC-based programs that implement floating-point math.

IEEE 754 specifies that numbers be stored in binary format to reduce storage requirements and allow the built-in binary arithmetic instructions that are available on all microprocessors to process the data in a relatively rapid fashion. However, some numbers that are simple, nonrepeating decimal numbers are converted into repeating binary numbers that cannot be stored with perfect accuracy.

For example, the number 1/10 can be represented in a decimal number system with a simple decimal:
.1
However, the same number in binary format becomes the repeating binary decimal:
.0001100011000111000111 (and so on)
This number cannot be represented in a finite amount of space. Therefore, this number is rounded down by approximately -2.78E-17 when it is stored.

If several arithmetic operations are performed to obtain a given result, these rounding errors may be cumulative.
« Last Edit: January 25, 2013, 10:31:33 am by Craig S »

Offline Craig S

  • KOP CONDUCTOR
  • Legacy Fan
  • ******
  • Posts: 4,979
Re: The Microsoft Excel Help Thread
« Reply #47 on: January 25, 2013, 10:27:14 am »
I don't think it can be that as I only input figures based on pounds and pence so always 2 digits after the decimal point.

I tried a google to see if anybody knew and it was a total mine field some people have straight forward solutions and others say this is wrong etc and that you shouldnt change certain settings because it will cause other problems else where but there are pages and pages on it.

drives me bonkers and my accountant too

There is a setting in calculation tabs "precision as displayed" that may fix it for you.  I don't like enabling this as it could open up loads of rounding issues.  If you are only entering whole pounds and pence and adding you may be ok. But beware if you divide anything into a fraction, (eg. a cost /3) it will be a whole clusterfuck

Offline Slick_Beef

  • RAWK's Master Baker
  • RAWK Supporter
  • Legacy Fan
  • ******
  • Posts: 9,087
Re: The Microsoft Excel Help Thread
« Reply #48 on: January 25, 2013, 01:14:27 pm »
There's definitely a mis-calculation within Excel, i have seen it on occasion. We use figures to 7 decimal places, and we round to 7 d.p before exporting data to Excel or mySQL. When the same calculated results are rounded to 1 d.p in both there is occasionally a 0.1 difference (not very often). When we have investigated, and calculated manually, it's always been Excel that is incorrect.

MS has this below, I'm not sure how that differs on the mySQL server though, but it's LAMP, so may differ storage.
http://support.microsoft.com/kb/214118

Interesting!

Offline GBF

  • RAWK Supporter
  • Legacy Fan
  • ******
  • Posts: 19,033
  • The only religion with a God that you can touch!
Re: The Microsoft Excel Help Thread
« Reply #49 on: March 12, 2013, 04:39:47 pm »
a dumb question...how to you sort numerically in excel without having 1, 10,11,etc coming first instead of 1,2,3,4...?
01111001 01101111 01110101 00100111 01101100 01101100 00100000 01101110 01100101 01110110 01100101 01110010 00100000 01110111 01100001 01101100 01101011 00100000 01100001 01101100 01101111 01101110 01100101

Offline TepidT2O

  • Deffo NOT 9"! MUFC bedwetter. Grass. Folically-challenged, God-piece-wearing, monkey-rubber. Jizz aroma expert. Operating at the lower end of the distribution curve...has the hots for Alan. Bastard. Fearless in transfer windows with lack of convicti
  • Lead Matchday Commentator
  • Legacy Fan
  • ******
  • Posts: 93,586
  • Dejan Lovren fan club member #1
Re: The Microsoft Excel Help Thread
« Reply #50 on: March 12, 2013, 05:34:37 pm »
Data--> filter

Then sort smallest to largest.


Or is that too obvious ?
“Happiness can be found in the darkest of times, if one only remembers to turn on the light.”
“Generosity always pays off. Generosity in your effort, in your work, in your kindness, in the way you look after people and take care of people. In the long run, if you are generous with a heart, and with humanity, it always pays off.”
W

Offline Demandred

  • Do not forsake me oh my darlin
  • Legacy Fan
  • ******
  • Posts: 1,640
  • I'll be waiting, with a gun & a pack of sandwiches
Re: The Microsoft Excel Help Thread
« Reply #51 on: March 13, 2013, 09:05:55 pm »
a dumb question...how to you sort numerically in excel without having 1, 10,11,etc coming first instead of 1,2,3,4...?

Sounds like you have numbers stored as text - select them all and go to format cells, check the number format isn't text or general, set to number. If you sort it should arrange them properly

Offline GBF

  • RAWK Supporter
  • Legacy Fan
  • ******
  • Posts: 19,033
  • The only religion with a God that you can touch!
Re: The Microsoft Excel Help Thread
« Reply #52 on: March 14, 2013, 08:44:18 am »
Sounds like you have numbers stored as text - select them all and go to format cells, check the number format isn't text or general, set to number. If you sort it should arrange them properly

cheers, that helped.  was making nuts the other day trying to sort out data.


Data--> filter

Then sort smallest to largest.


Or is that too obvious ?

what I kept doing but didnt help because of the column was not set as numbers as Demandred mentioned
01111001 01101111 01110101 00100111 01101100 01101100 00100000 01101110 01100101 01110110 01100101 01110010 00100000 01110111 01100001 01101100 01101011 00100000 01100001 01101100 01101111 01101110 01100101

Offline CHOPPER

  • Bad Tranny with a Chopper. Hello John gotta new Mitre? I'm Jim Davidson in disguise. Undercover Cop (Grammar Division). Does Louis Spence. Well. A giga-c*nt worth of nothing in particular. Hodgson apologist. Astronomical cock. Hug Jacket Distributor
  • Legacy Fan
  • ******
  • Posts: 52,393
  • Super Title: Not Arsed
Re: The Microsoft Excel Help Thread
« Reply #53 on: February 3, 2014, 05:44:52 pm »
Hello all, wondering if you can help out a bad gonk with an Excel problem.

I have a vary basic knowledge and use of Excel and generally just make up some tracker sheets I distribute out to my team (Engineering) so we can track work streams and target specific pieces of kit.

Problem I have is, I need to target some specif pieces of data, this data(machine performance) is held in an Excel format and from last year, which was recorded weekly(by the operator and operations admin) for each line and totalised into one 'weekly' Excel spreads sheet.
Each week was stored in its own folder. So I have 52 folders holding specific bits of totalised info for a week that I need to gather together in one big total so I can target specific areas of equipment for the upcoming year- how do I gather them all together to get the total number of instances for each area I want to look at?

I hope that makes sense.

@ Veinticinco de Mayo The way you talk to other users on this forum is something you should be ashamed of as someone who is suppose to be representing the site.
Martin Kenneth Wild - Part of a family

Offline Thush

  • Spawwow, Tit. Anal Chat is "Equidistant between chit-chat and analysis"
  • RAWK Supporter
  • Legacy Fan
  • ******
  • Posts: 3,072
  • It's pronounced "Toosh"
Re: The Microsoft Excel Help Thread
« Reply #54 on: February 3, 2014, 06:28:52 pm »
If you have your main summary spreadsheet open and the various different weeks' worth of spreadsheets (can do this in batches or even one week-at-a-time), you can put "=" in one cell in your summary sheet, then go to the relevant weekly sheet and choose which bit of data you need.

Your formula in the summary sheet will look something like "=[Week01.xls]Machines!A2" if you wanted to choose cell A2 in the Machines worksheet in the Week01.xls spreadsheet.

http://www.excel-easy.com/examples/external-references.html

Hope that was what you were after.

Offline CHOPPER

  • Bad Tranny with a Chopper. Hello John gotta new Mitre? I'm Jim Davidson in disguise. Undercover Cop (Grammar Division). Does Louis Spence. Well. A giga-c*nt worth of nothing in particular. Hodgson apologist. Astronomical cock. Hug Jacket Distributor
  • Legacy Fan
  • ******
  • Posts: 52,393
  • Super Title: Not Arsed
Re: The Microsoft Excel Help Thread
« Reply #55 on: February 3, 2014, 07:20:22 pm »
I'm going to try this.

Much appreciated for the pointer.
@ Veinticinco de Mayo The way you talk to other users on this forum is something you should be ashamed of as someone who is suppose to be representing the site.
Martin Kenneth Wild - Part of a family

Offline TepidT2O

  • Deffo NOT 9"! MUFC bedwetter. Grass. Folically-challenged, God-piece-wearing, monkey-rubber. Jizz aroma expert. Operating at the lower end of the distribution curve...has the hots for Alan. Bastard. Fearless in transfer windows with lack of convicti
  • Lead Matchday Commentator
  • Legacy Fan
  • ******
  • Posts: 93,586
  • Dejan Lovren fan club member #1
Re: The Microsoft Excel Help Thread
« Reply #56 on: February 3, 2014, 09:00:21 pm »
Fucking hell

I've really learned something in this thread.


Chopper has a job!
“Happiness can be found in the darkest of times, if one only remembers to turn on the light.”
“Generosity always pays off. Generosity in your effort, in your work, in your kindness, in the way you look after people and take care of people. In the long run, if you are generous with a heart, and with humanity, it always pays off.”
W

Offline CHOPPER

  • Bad Tranny with a Chopper. Hello John gotta new Mitre? I'm Jim Davidson in disguise. Undercover Cop (Grammar Division). Does Louis Spence. Well. A giga-c*nt worth of nothing in particular. Hodgson apologist. Astronomical cock. Hug Jacket Distributor
  • Legacy Fan
  • ******
  • Posts: 52,393
  • Super Title: Not Arsed
Re: The Microsoft Excel Help Thread
« Reply #57 on: February 4, 2014, 07:23:26 pm »
Fucking hell

I've really learned something in this thread.


Chopper has a job!
Yes, I'm full of surprises.

On the back of all this I've downloaded a full version of 'Lynda Excel(3courses)' and will be a master of it by the end of spring. I'm that good. Honest ;)



@ Veinticinco de Mayo The way you talk to other users on this forum is something you should be ashamed of as someone who is suppose to be representing the site.
Martin Kenneth Wild - Part of a family

Offline Red Raw

  • RAWK Supporter
  • Legacy Fan
  • ******
  • Posts: 4,821
  • Klopptimistic
Re: The Microsoft Excel Help Thread
« Reply #58 on: February 8, 2014, 02:42:08 am »
Lynda Excel sounds very dirty - does she do house calls?

Offline Something Else

  • that car's fine lookin' man (clearly insured with confused.com)
  • RAWK Supporter
  • Legacy Fan
  • ******
  • Posts: 33,204
  • Bazinga
  • Super Title: something else required
Re: The Microsoft Excel Help Thread
« Reply #59 on: March 26, 2014, 10:24:42 am »
Can anyone help.

I have data with a date opened and a date closed. These Twp dates can span a range of many months and are open to any dates.

I want to be able to say how many events are open each month.

Eg if my date range is 11/3/2014 open, 11/6/2014 closed, I want it to bring in results that its open , march, april  may June etc.

Would have each month as a separate column.

Any help appreciated. Thank you.

Offline Party Phil

  • Boring Cunt that flies Air Bizarre
  • RAWK Supporter
  • Legacy Fan
  • ******
  • Posts: 12,552
  • Big in Japan
Re: The Microsoft Excel Help Thread
« Reply #60 on: March 26, 2014, 10:45:31 am »
If I understand correctly what you're after, I would create a table with the start date of each month in the first row and then use a formula as below to identify if the event was open in each month:

=IF(AND(($A2<=C$1),($B2>=C$1)),"Y","N")

Table would look something like the below

Start dateEnd date01/01/1401/02/1401/03/1401/04/14...
11/03/201411/06/14NNYY...
25/01/201421/03/14YYYN...
15/01/201421/01/14YNNN...

To see how many events are open in the month you can use a SUMIF for each column to count the number of Ys.
If you're lying, I'll chop your head off.

Offline Hazzak

  • Main Stander
  • ***
  • Posts: 66
  • We all Live in a Red and White Kop
Re: The Microsoft Excel Help Thread
« Reply #61 on: April 3, 2014, 11:18:18 am »
Hi folks,

Hoping someone may be able to help me with an excel calculation that I cannot seem to even get close to (If it is possible).
I have a formula that takes an hourly rate (A), adds onto this the hourly holiday entitlement (B) and then from this, provides an hourly national insurance value. 
The first part is straight forward just giving a rate Total (A+B) before the NI calculation.

To get the estimated hourly NI, I multiply the combined rate (A+B) by 37 (37 is Average hours worked),
I then remove the National Insurance threshold (£153)
I multiply this total by 13.8% (NI %)
And then finally divide this total by 37 to get an estimated hourly NI Value.

A + B = C
C*37 = D
D-153 = E
E *13.8% = F
F/37 = G

My excel example “=((((B9+D9)*37)-153)*13.8%)/37)”

This works as I need, however, I would like to know if it is possible to identify C, if all I have is G?

Thanks for looking, any thoughts appreciated!
« Last Edit: April 3, 2014, 11:23:14 am by Hazzak »

Offline GBF

  • RAWK Supporter
  • Legacy Fan
  • ******
  • Posts: 19,033
  • The only religion with a God that you can touch!
Re: The Microsoft Excel Help Thread
« Reply #62 on: April 3, 2014, 12:23:36 pm »
A + B = C
C*37 = D
D-153 = E
E *13.8% = F
F/37 = G
This works as I need, however, I would like to know if it is possible to identify C, if all I have is G?


using maths' substitution theory...

C = D/37 = (E+153)/37 = ....
« Last Edit: April 3, 2014, 12:25:56 pm by GBF »
01111001 01101111 01110101 00100111 01101100 01101100 00100000 01101110 01100101 01110110 01100101 01110010 00100000 01110111 01100001 01101100 01101011 00100000 01100001 01101100 01101111 01101110 01100101

Offline Hazzak

  • Main Stander
  • ***
  • Posts: 66
  • We all Live in a Red and White Kop
Re: The Microsoft Excel Help Thread
« Reply #63 on: April 3, 2014, 01:52:30 pm »
Many thanks GBF, I now have that and can take G and get back to A.
I do however need one more step which is where I think I am losing it....

A + G = H

If I have H, is it possible to calculate G when I don’t have A?

The reason revolves around me having a gross amount paid to someone who is National Insurance exempt and therefore receives an hourly rate with the NI they would be paying on top (A + G).

For example £16.75 Gross. (Calculated as £15.00 plus NI of £1.75)

£16.75 calculated as per my excel formula:

£15.00 + £1.81 (Holiday pay at 12.07%) = £16.81
£16.81 *37 = £621.97 (weekly Pay)
£621.97 – 153 = £468.97 (Weekly pay after NI reduction)
£468.97 * 13.8% = 64.72 (Weekly NI)
£64.72 /37 = £1.75 (Hourly NI)

£1.75 +£15.00 = £16.75 (Hourly rate including the NI)

So if I have £16.75, can I get back to £15.00 with a formula?

Offline GBF

  • RAWK Supporter
  • Legacy Fan
  • ******
  • Posts: 19,033
  • The only religion with a God that you can touch!
Re: The Microsoft Excel Help Thread
« Reply #64 on: April 3, 2014, 02:51:57 pm »
Many thanks GBF, I now have that and can take G and get back to A.
I do however need one more step which is where I think I am losing it....

A + G = H

If I have H, is it possible to calculate G when I don’t have A?


do you have B and any components of C (C, D, E of F)?
01111001 01101111 01110101 00100111 01101100 01101100 00100000 01101110 01100101 01110110 01100101 01110010 00100000 01110111 01100001 01101100 01101011 00100000 01100001 01101100 01101111 01101110 01100101

Offline Hazzak

  • Main Stander
  • ***
  • Posts: 66
  • We all Live in a Red and White Kop
Re: The Microsoft Excel Help Thread
« Reply #65 on: April 3, 2014, 03:09:03 pm »
Afraid not, this is where i think i will come unstuck.

B is a variable based on A, (Hourly Rate X Holiday percentage) and all other letters are based on the static of 153 (NI Free-pay per week).
Thanks again for looking.

Offline hixxstar

  • RAWK Supporter
  • Legacy Fan
  • ******
  • Posts: 3,593
  • Dont Worry I'm From The Internet
Re: The Microsoft Excel Help Thread
« Reply #66 on: April 7, 2014, 11:41:20 pm »
Not sure if this is any help to you ?.....

http://www.microsofttraining.net/microsoft-training-manuals.php
Shanks on Leaving Liverpool FC

"It was the most difficult thing in the world, when I went to tell the chairman........ It was like walking to the electric chair.... That's the way it felt."

Offline Craig S

  • KOP CONDUCTOR
  • Legacy Fan
  • ******
  • Posts: 4,979
Re: The Microsoft Excel Help Thread
« Reply #67 on: April 8, 2014, 01:58:36 pm »
You could try using Goal Seek:
http://www.homeandlearn.co.uk/excel2007/excel2007s7p4.html

But it sounds like you will have multiple (perhaps 100s of) correct solutions. I think goal seek would only return the first one

Offline PaulF

  • https://www.justgiving.com/fundraising/paulfelce
  • RAWK Supporter
  • Legacy Fan
  • ******
  • Posts: 21,575
  • Nothing feels as good as fat tastes.
Re: The Microsoft Excel Help Thread
« Reply #68 on: April 8, 2014, 05:15:15 pm »
Many thanks GBF, I now have that and can take G and get back to A.
I do however need one more step which is where I think I am losing it....

A + G = H

If I have H, is it possible to calculate G when I don’t have A?

The reason revolves around me having a gross amount paid to someone who is National Insurance exempt and therefore receives an hourly rate with the NI they would be paying on top (A + G).

For example £16.75 Gross. (Calculated as £15.00 plus NI of £1.75)

£16.75 calculated as per my excel formula:

£15.00 + £1.81 (Holiday pay at 12.07%) = £16.81
£16.81 *37 = £621.97 (weekly Pay)
£621.97 – 153 = £468.97 (Weekly pay after NI reduction)
£468.97 * 13.8% = 64.72 (Weekly NI)
£64.72 /37 = £1.75 (Hourly NI)

£1.75 +£15.00 = £16.75 (Hourly rate including the NI)

So if I have £16.75, can I get back to £15.00 with a formula?


Could you use a vlookup?  Populate a table first with all the reasonable hourly rates and calculate your final value ( I think we've got as far as H in the alphabet).  Then all you have to do is lookup up the final value and you'll have the starting value?
"All the lads have been talking about is walking out in front of the Kop, with 40,000 singing 'You'll Never Walk Alone'," Collins told BBC Radio Solent. "All the money in the world couldn't buy that feeling," he added.

Offline kavah

  • the Blacksmith. Definitely NOT from Blackpool!
  • RAWK Supporter
  • Legacy Fan
  • ******
  • Posts: 19,634
  • We all Live in a Red and White Kop
Re: The Microsoft Excel Help Thread
« Reply #69 on: November 6, 2014, 10:33:24 am »
How do I use conditional formatting so a number populates text in a different cell

Eg >10,000 < 14,500 = "low"
 14,500 or greater = "high"

Thanks

Offline PaulF

  • https://www.justgiving.com/fundraising/paulfelce
  • RAWK Supporter
  • Legacy Fan
  • ******
  • Posts: 21,575
  • Nothing feels as good as fat tastes.
Re: The Microsoft Excel Help Thread
« Reply #70 on: November 6, 2014, 10:50:45 am »
I don't think you can do that with conditional formatting. You can use an "if" formula to do that.
eg

=if(A1<14500,"low","high")

Which, as per the prompt, basically says, if the value in A1 < 14500, put the text "low" , otherwise put the text "high".

I think there might be a way to do multiple versions , low, med , high, but I'll have to think about that!

"All the lads have been talking about is walking out in front of the Kop, with 40,000 singing 'You'll Never Walk Alone'," Collins told BBC Radio Solent. "All the money in the world couldn't buy that feeling," he added.

Offline kavah

  • the Blacksmith. Definitely NOT from Blackpool!
  • RAWK Supporter
  • Legacy Fan
  • ******
  • Posts: 19,634
  • We all Live in a Red and White Kop
Re: The Microsoft Excel Help Thread
« Reply #71 on: November 6, 2014, 11:09:37 am »
I don't think you can do that with conditional formatting. You can use an "if" formula to do that.
eg

=if(A1<14500,"low","high")

Which, as per the prompt, basically says, if the value in A1 < 14500, put the text "low" , otherwise put the text "high".

I think there might be a way to do multiple versions , low, med , high, but I'll have to think about that!



Great cheers. That's a big help. I'll have a try with that.

The 3 options high / low / medium would be boss

Thanks again
« Last Edit: November 6, 2014, 11:12:46 am by kavah »

Offline PaulF

  • https://www.justgiving.com/fundraising/paulfelce
  • RAWK Supporter
  • Legacy Fan
  • ******
  • Posts: 21,575
  • Nothing feels as good as fat tastes.
Re: The Microsoft Excel Help Thread
« Reply #72 on: November 6, 2014, 11:27:46 am »
What are the values you want for low , medium, high? I'm not much good at abstract stuff.

"All the lads have been talking about is walking out in front of the Kop, with 40,000 singing 'You'll Never Walk Alone'," Collins told BBC Radio Solent. "All the money in the world couldn't buy that feeling," he added.

Offline kavah

  • the Blacksmith. Definitely NOT from Blackpool!
  • RAWK Supporter
  • Legacy Fan
  • ******
  • Posts: 19,634
  • We all Live in a Red and White Kop
Re: The Microsoft Excel Help Thread
« Reply #73 on: November 6, 2014, 12:01:47 pm »
What are the values you want for low , medium, high? I'm not much good at abstract stuff.




it's to produce a table which rates a product as A (good), B (not so good) or C (rubbish) based on customer preferences.

there are several different criteria for example  price - typically $20 - $30. so $20.00 to $23.33 would be classed as A, $23.34 to $26.66 would be B and $26.77 to $30.00 would be C.

or mean time before failure (MTBF) typically 10,000 hours to 15,000 hours. So  10,000 - 11,667 hours would get a customer rating of C. 11,668 - 12,834 would be  B and 12,833 - 15,000 A

(It's for a business game I'm doing)

Offline PaulF

  • https://www.justgiving.com/fundraising/paulfelce
  • RAWK Supporter
  • Legacy Fan
  • ******
  • Posts: 21,575
  • Nothing feels as good as fat tastes.
Re: The Microsoft Excel Help Thread
« Reply #74 on: November 6, 2014, 12:30:18 pm »
ok, so you have three columns? The first is product, the second is price and the third is MTBF , and you want to rate the product on either column b or c.  How do you decide? 
"All the lads have been talking about is walking out in front of the Kop, with 40,000 singing 'You'll Never Walk Alone'," Collins told BBC Radio Solent. "All the money in the world couldn't buy that feeling," he added.

Offline Worgie

  • Main Stander
  • ***
  • Posts: 123
Re: The Microsoft Excel Help Thread
« Reply #75 on: November 6, 2014, 12:47:51 pm »
it's to produce a table which rates a product as A (good), B (not so good) or C (rubbish) based on customer preferences.

there are several different criteria for example  price - typically $20 - $30. so $20.00 to $23.33 would be classed as A, $23.34 to $26.66 would be B and $26.77 to $30.00 would be C.

[/quote]


How does this look, assuming your price is in cell A1?

=IF(A1<=23.33,"A",IF(A1>=26.77,"C","B"))
RAI TV Commentator May 2005 - "If Milan score in the first 20 minutes they will win!"  Yeah Right!

Offline kavah

  • the Blacksmith. Definitely NOT from Blackpool!
  • RAWK Supporter
  • Legacy Fan
  • ******
  • Posts: 19,634
  • We all Live in a Red and White Kop
Re: The Microsoft Excel Help Thread
« Reply #76 on: November 6, 2014, 12:54:13 pm »
ok, so you have three columns? The first is product, the second is price and the third is MTBF , and you want to rate the product on either column b or c.  How do you decide? 

I'll have the product  and then rate it for price and MTBF and age. So something like:

Product: Kavah's gadget, Price $21.00, Rating A, MTBF 10,000 hours, Rating C, Age 5.3 years rating B.
and it will look  something like:

Kavah's gadget
Price: $21.00 A.
MTBF: 10,000 C.
Age 5.3 B.


Offline Worgie

  • Main Stander
  • ***
  • Posts: 123
Re: The Microsoft Excel Help Thread
« Reply #77 on: November 6, 2014, 12:59:21 pm »
I'll have the product  and then rate it for price and MTBF and age. So something like:

Product: Kavah's gadget, Price $21.00, Rating A, MTBF 10,000 hours, Rating C, Age 5.3 years rating B.
and it will look  something like:

Kavah's gadget
Price: $21.00 A.
MTBF: 10,000 C.
Age 5.3 B.



So it will be three different  =if    formulas for each, based on the template I posted above?
RAI TV Commentator May 2005 - "If Milan score in the first 20 minutes they will win!"  Yeah Right!

Offline kavah

  • the Blacksmith. Definitely NOT from Blackpool!
  • RAWK Supporter
  • Legacy Fan
  • ******
  • Posts: 19,634
  • We all Live in a Red and White Kop
Re: The Microsoft Excel Help Thread
« Reply #78 on: November 6, 2014, 01:12:00 pm »
it's to produce a table which rates a product as A (good), B (not so good) or C (rubbish) based on customer preferences.

there are several different criteria for example  price - typically $20 - $30. so $20.00 to $23.33 would be classed as A, $23.34 to $26.66 would be B and $26.77 to $30.00 would be C.




How does this look, assuming your price is in cell A1?

=IF(A1<=23.33,"A",IF(A1>=26.77,"C","B"))

Yes! thanks so much

this does it

=IF(C28>23.33,"B",IF(C28>26.67,"C","A"))

I usually outsource this kind of thing to Mrs. Kavah but she's inconsiderately gone out of town :)

Cheers chaps - owe you both  a beer when you come to Saigon
« Last Edit: November 6, 2014, 01:14:25 pm by kavah »

Offline PaulF

  • https://www.justgiving.com/fundraising/paulfelce
  • RAWK Supporter
  • Legacy Fan
  • ******
  • Posts: 21,575
  • Nothing feels as good as fat tastes.
Re: The Microsoft Excel Help Thread
« Reply #79 on: November 6, 2014, 01:58:05 pm »
Yes! thanks so much

this does it

=IF(C28>23.33,"B",IF(C28>26.67,"C","A"))

I usually outsource this kind of thing to Mrs. Kavah but she's inconsiderately gone out of town :)

Cheers chaps - owe you both  a beer when you come to Saigon

My birthplace, never felt the need to go back though.  Are you a native, or a visitor?
"All the lads have been talking about is walking out in front of the Kop, with 40,000 singing 'You'll Never Walk Alone'," Collins told BBC Radio Solent. "All the money in the world couldn't buy that feeling," he added.