I can't get this simple division problem to work

bucci35

Active Member
Joined
Jul 6, 2002
Messages
323
cell a1 has a percentage that can be inputed. lets say i put 20% in.
cell b4=$215
cell b5=$245

cell b10=if(b5-b4)/b5>a1,b5,0)

This won't work for me!! any ideas?
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

bucci35

Active Member
Joined
Jul 6, 2002
Messages
323
Thanks, that works perfectly. Now, any idea why when I add the statement at the end beginning with ,if, it doesn't work? The formula stays black



SUM(IF('Sale Data'!$F$7:'Sale Data'!$F$10537='Sale Data'!$B$2,INDEX('Sale Data'!$J$7:'Sale Data'!$P$10537,0,MATCH('Sale Data'!J$6,'Sale Data'!$J$6:'Sale Data'!$P$6,0))))/16,if(('Sale Data'!C4-'Sale Data'!C3)/'Sale Data'!C4>'Sheet 3'!a1,'Sale Data'!C4,0)
 

bucci35

Active Member
Joined
Jul 6, 2002
Messages
323
Let me explain further:

I want to take the results of this:
=SUM(IF('Sale Data'!$F$7:'Sale Data'!$F$10537='Sale Data'!$B$2,INDEX('Sale Data'!$J$7:'Sale Data'!$P$10537,0,MATCH('Sale Data'!J$6,'Sale Data'!$J$6:'Sale Data'!$P$6,0))))/16
Which equals $90.46,
And the resutls of this:
=SUM(IF($A$7:$A$10537=$B$4,INDEX($J$7:$P$10537,0,MATCH(J$6,$J$6:$P$6,0))))
Which equals $537.03
and say,
=IF(('Sale Data'!C4-'Sale Data'!C3)/'Sale Data'!C4>A1,'Sale Data'!C4,0)
Where a1 is where I enter a percentage

and have the cell I type this in display the answer.

Hope this isn't too confusing!!
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
20,527
Office Version
  1. 365
Platform
  1. Windows
The intent is not clear, but if 'Sale Data'!C3 contains the first SUM(IF(...) formula, and 'Sale Data'!C4 contains the second one, couldn't you simply use that last IF formula in a separate cell?
 

bucci35

Active Member
Joined
Jul 6, 2002
Messages
323
Yes I could, but the problem is their will be tons of these cells and I didn't want to have to duplicate the "tons" *2!

The $90.46 is the average of all stores
The $537.03 is the total of store 1
I want to be able to have the user type a percentage in cell a1 and have the figure of the stores show the amount if it is <or> the percentage of the chain based on the percentage typed in cell a1.
 

Forum statistics

Threads
1,176,458
Messages
5,903,220
Members
435,015
Latest member
arogya74

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top