1

This is a math question in the context of an Excel spreadsheet. I have an Excel spreadsheet with 10 rows, where I am dividing the value in ColumnB by the value in ColumnA to create a new number like this

        A       B        New Number 
Bill    1.14    156      136.842  (156 / 1.14)

The new number ( as I call it ) is being used to compare each of the rows against each other i.e. I am sorting from biggest to smallest based on this "new number"

I am running into confusion when the number in columnA is less than 1. If it's less than 1 do I still divide by the value in ColumnA or do I multiply by the value in ColumnA? You can see from below, that the output will differ significantly

Mike    0.48   250      520 (250 / .48)

or

Mike    0.48   250      120 (250 * .48)

In my head, I am thinking that I must be stupid. If I divide by the value in ColumnA for the first 9 rows (where value in columnA is greater than 1), then I also have to do it for the 10th row (where value in columnA is less than 1), however, the total of 120 (reached by multiplying) makes more sense intuitively than the total of 520 (reached by dividing) that now I'm totally confused if different rules apply when the number is between 0 and 1 and I should indeed be multiplying.

Question 1: if the number for value in columnA is between 0 and 1, do I need to divide or multiply columnB by the value in ColumnA (if I want the sort comparison for all ten rows from greatest to least to make sense)

Part 2: if I do indeed need to multiply for that 1 row (and divide for all 9 others), is there a way in Excel to apply a formula to all 10 rows that takes into account that in one row the operator changes?

Sample Data

        ColumnA ColumnB  Total.
Mike    0.48   175       84       175 * .48  (Or do I still divide?
Bill    1.14    156      136.842  156 / 1.14
Simon   2.02    189      93.56    189 / 2.02
Jim     2.56    112      43.75    112 / 2.56
Michael
  • 191
  • Not sure what you are trying to achieve but yes if you divide by a number between 0 and 1 then obviously you get a higher value. In Excel you could make use of the if function with test clause Ax < 1, if you really wish to, and then divide by Ax or multiply with it based on Ax<1 being true or false. – Maestro13 Oct 27 '15 at 17:12
  • @Maestro13 I think you misunderstood the question. I am asking "what am I required to do" by the laws of Math if my ultimate goal is to sort the list (i.e. compare the 10 rows) from greatest to least. If for the 9 rows where I am dividing by the value in column A (where the value is greater than 1) am I required for the 10th row (where the value is between 0 and 1) to do the same (i.e. divide), or should I multiply? – Michael Oct 27 '15 at 17:17

1 Answers1

1

When you divide you are basically asking the question: what percent is this number of another number? So when you divide 156 by 1.14, you are saying: 156 is 114% of what number? Well its 114% of 136.842. So If that's what you're trying to do, then don't change your formula unless instructed to by a book (if this is for homework) or a mentor to the project.

If you needed to have a condition taken into account (such as if the number was between 0 and 1) then you can use the =if() statement. In this case you would do =if(0

Dylan
  • 26
  • so by dividing 250 by .48 I'm asking what am I asking 250 is 50% of what number? – Michael Oct 27 '15 at 17:26
  • Correct. In algebra I would set the problem up in proportions. So one one side I would have 250/.48 and then on the other x/1. Like: 250/.48=x/1 --> 250=.48X --> X=520 so 250 is 48% of 520 which is 100%. X=250/.48 – Dylan Oct 27 '15 at 17:38
  • just out of curiosity, what would question would I basically be asking if I multiplied instead of divided? – Michael Oct 28 '15 at 19:07
  • If you multiply a number by a percent (decimal) then you are asking something like this: 520*.48=250 This is basically saying: what is 48% of 520? – Dylan Oct 28 '15 at 19:19