Excel Problem

vivienne3608

New Member
Joined
Sep 10, 2014
Messages
17
Good morning,

I am hoping someone can help me. I need the formula to try and pick the closest greater proposed value to the current value and the previous formula I had worked =MIN(IF(A2:A15=H2,IF(B1:E1=H3,IF(B2:E15>=H4,B2:E15)))). However I have now noticed that in my larger data table (the table displayed is just a snapshot) the current value could be higher than the largest value for the proposed value options. I therefore need the formula to do as the above formula but to also pick the highest proposed value for that particular group and stage if there is no higher option than the current value. The following formula returns a zero figure:</SPAN></SPAN>

=MIN(IF($A$2:$A$15=H2,IF(INDEX($B$2:$E$15,0,MATCH(H3,$B$1:$E$1,0))>=H4,</SPAN></SPAN>
INDEX($B$2:$E$15,0,MATCH(H3,$B$1:$E$1,0)))))</SPAN></SPAN>

The table below shows the higher current value and the proposed amount I need the formula to return. I know the proposed value I need appears twice, however, it doesn’t matter which cell the formula picks. The cells B2:E15 contain the proposed amounts. (</SPAN></SPAN>The proposed amount should be equal or higher than the current amount. However there are a few instances when the current amount is higher than the largest proposed option. In that case I want excel to pick that largest proposed option even if it is not equal or greater than the current amount).</SPAN>


A</SPAN>B</SPAN>C</SPAN>D</SPAN>E</SPAN>F</SPAN>G</SPAN>H</SPAN>
1</SPAN>1</SPAN>2</SPAN>3</SPAN>4</SPAN>
2</SPAN>Top</SPAN>37,930</SPAN>39,830</SPAN>41,830</SPAN>44,130</SPAN>Group</SPAN>Bottom</SPAN>
3</SPAN>Top</SPAN>37,930</SPAN>39,830</SPAN>41,830</SPAN>44,130</SPAN>Stage</SPAN>1</SPAN>
4</SPAN>Top</SPAN>37,245</SPAN>39,145</SPAN>41,045</SPAN>43,345</SPAN>Current Amount</SPAN>34,719</SPAN>
5</SPAN>Top</SPAN>36,459</SPAN>38,359</SPAN>40,259</SPAN>42,559</SPAN>Proposed Amount</SPAN>33,334</SPAN>
6</SPAN>Top</SPAN>35,774</SPAN>37,574</SPAN>39,374</SPAN>41,674</SPAN>
7</SPAN>Top</SPAN>34,988</SPAN>36,688</SPAN>38,488</SPAN>40,688</SPAN>
8</SPAN>Top</SPAN>34,988</SPAN>36,688</SPAN>38,488</SPAN>40,688</SPAN>
9</SPAN>Bottom</SPAN>33,334</SPAN>34,834</SPAN>36,534</SPAN>38,634</SPAN>
10</SPAN>Bottom</SPAN>33,334</SPAN>34,834</SPAN>36,534</SPAN>38,634</SPAN>
11</SPAN>Bottom</SPAN>32,758</SPAN>34,158</SPAN>35,758</SPAN>37,858</SPAN>
12</SPAN>Bottom</SPAN>32,182</SPAN>33,582</SPAN>35,082</SPAN>37,182</SPAN>
13</SPAN>Bottom</SPAN>31,507</SPAN>32,807</SPAN>34,007</SPAN>35,907</SPAN>
14</SPAN>Bottom</SPAN>30,931</SPAN>32,031</SPAN>33,131</SPAN>34,731</SPAN>
15</SPAN>Bottom</SPAN>30,931</SPAN>32,031</SPAN>33,131</SPAN>34,731

<TBODY>
</TBODY>
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

vivienne3608

New Member
Joined
Sep 10, 2014
Messages
17
Hello,

I no longer need any further replies to this issue. Thank you to all who have taken the time to try and find a solution for me I appreciate it.

Vivienne​
 

Watch MrExcel Video

Forum statistics

Threads
1,108,614
Messages
5,523,902
Members
409,542
Latest member
Shezz01

This Week's Hot Topics

Top